Choose {1,2}

[marisa01]

Hello can someone help me with this formula,

I am trying to understand if this is an array or a regular formula Choose {1,2}, what does that mean?

Thank you in advance.

If(Isna(Vllookup(A3, Choose {1,2}

 

[Jonmo1]

Welcome to the board.

Well, we'd have to see the entire actual formula to really give a definitive answer.

But it looks like a method that is used to cheat Vlookup into reading backwards.
Vlookup can only find a match in one column, and return a value from a column 'to the right' of where the match is found.
It Cannot return a value from a column 'to the left' of where the match is found.

But there are methods using the CHOOSE function to rebuild a range so that Column B is actually on the LEFT side of Column A.

 

[marisa01]

Hi Jonmo1

Thank you very much. I will look at it again. Very much appreciated

 

[barry houdini]

You can also do a similar thing with IF function, e.g.

=VLOOKUP(A2,IF({1,0},C2:C10,B2:B10),2,0)

that looks up A2 in C2:C10 and returns the corresponding value from B2:B10

It's a nice trick, as is the CHOOSE option, but if you want your formulas to be easily understood and modified (possibly by others), then it would probably be better to use a more "conventional" INDEX/MATCH approach like

=INDEX(B2:B10,MATCH(A2,C2:C10,0))

 

[marisa01]

This is great. Thank you so much for the details.

 

[Jonmo1]

You're welcome.

 

[Jonmo1]

then it would probably be better to use a more "conventional" INDEX/MATCH approach like

=INDEX(B2:B10,MATCH(A2,C2:C10,0))

+1