Is it possible to use an array within a Google Sheets function

Question

Is it possible to have an array within a formula, as opposed to referencing the array in cells.

For example with VLOOKUP, I have tried to do this but it doesn't work:

=VLOOKUP(I2,[["Superking",4],["King",3],["Double",2],["Single",1]],2,0)

I'm essentially trying to incorporate the below table into the formula itself:

Superking      4
-----------------------------
King           3
-----------------------------
Double         2
-----------------------------
Single         1

I thought that this is maybe possisble but expressing the array incorrectly.

I do know that this can easily be done by putting the actual data into the sheet, but I am interested to know if it is posisble to do it this way.

JohnA · Accepted Answer · 2021-08-30 19:41:22Z

3

This seems to work:

=vlookup("Pete", {{"John",9};{"Pete", 2}}, 2, false)

answered Aug 30, 2021 at 19:41

JohnA

1,1136 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Caney · Accepted Answer · 2021-08-31 00:09:11Z

1

Yeah, this is the format to do it.

={"one","two","three","four","five";1,2,3,4,5}

will produce

edited Aug 31, 2021 at 0:09

Jeremy Caney

7,797115 gold badges58 silver badges87 bronze badges

answered Aug 30, 2021 at 19:42

Gabriel Pierce

4064 silver badges13 bronze badges

Collectives™ on Stack Overflow

Is it possible to use an array within a Google Sheets function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related