Reverse Vlookup Using Choose Function

[Excel Fox]

Country
Capital


India
New Delhi


Great Britain
London


Malaysia
Kuala Lumpur


China
Hong Kong



If you have to reverse vlookup a data table, you can a combination of INDEX and MATCH.

Another way to do this is by using a combination of VLOOKUP and CHOOSE

The following formula can be used for the table above to return the country name corresponding to the capital named that is used as the lookup value.

=VLOOKUP("London",CHOOSE({2,1},A2:A5,B2:B5),2,0). Note how the index of the two ranges are reversed within the CHOOSE function.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

Country

Capital



India

New Delhi



Great Britain

London



Malaysia

Kuala Lumpur



China

Hong Kong




If you have to reverse vlookup a data table, you can a combination of INDEX and MATCH.

Another way to do this is by using a combination of VLOOKUP and CHOOSE

The following formula can be used for the table above to return the country name corresponding to the capital named that is used as the lookup value.

=VLOOKUP("London",CHOOSE({2,1},A2:A5,B2:B5),2,0). Note how the index of the two ranges are reversed within the CHOOSE function.
I'm a little puzzled as to why you reversed the order of both arguments tot he CHOOSE function... this normally ordered CHOOSE function appears to return the same values as the formula you posted...

=VLOOKUP("London",CHOOSE({1,2},B2:B5,A2:A5),2,0)

Am I missing something here?

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Rick, I was only trying to emphasize that you can reverse the position of a columns in a lookup array by passing the index also as an array. The idea is that VLOOKUP can be used to lookup at a column that is on the right side of the column from which we expect the output.

[Rick Rothstein]

Rick, I was only trying to emphasize that you can reverse the position of a columns in a lookup array by passing the index also as an array. The idea is that VLOOKUP can be used to lookup at a column that is on the right side of the column from which we expect the output.
Okay, I can see what you are saying, but doesn't presenting the table array produced by the CHOOSE function in normal order do the same thing? Note that we both used 2 as the lookup column even though (supposedly) your table array is the reverse of mine. I think Excel may be "straightening out" (reordering) the table array it presents to VLOOKUP behind the scenes, similar to what happnes if you enter a range backward in a formula, for example, enter =SUM(B1:A10) and Excel will straighten that reference out so that it reads A1:B10. Of course that is just speculation on my part as formulas are not my main strength in Excel (I'd like to think VBA coding is).

[Excel Fox]

Maybe not. CHOOSE({1,2},B2:B10,A2:A10) is the same as CHOOSE({2,1},A2:A10,B2:B10) but not the same as CHOOSE({1,2},A2:A10,B2:B10)

[Rick Rothstein]

Maybe not. CHOOSE({1,2},B2:B10,A2:A10) is the same as CHOOSE({2,1},A2:A10,B2:B10) but not the same as CHOOSE({1,2},A2:A10,B2:B10)
I was not referring to the order CHOOSE uses to select from among its arguments (that order is a fixed... 1 refers the second argument, 2 refers to the 3rd argument, etc.), rather, I was referring to the array of cells that CHOOSE presents to VLOOKUP as a result of executing its (implied) array formula. I am guessing you were thinking (originally) CHOOSE({1,2},B2:B10,A2:A10) presents Column B followed by Column A to the VLOOKUP function thereby reversing VLOOKUP's normal order of processing columns, whereas CHOOSE({2,1},A2:A10,B2:B10) presents Column A followed by Column B... all I am saying is that either presents Column A followed by Column B to VLOOKUP (hence, we both used 2 as the "lookup" column), so that there was no need to reverse the numerical order of the array elements in the first argument so long as you make each of them refer to the correct column. Or did I simply misunderstand your original intent?

[Excel Fox]

1. There is no need to reverse the order of the array elements in the first argument, provided you've used the required order in the subsequent arguments
2. CHOOSE({1,2},B2:B10,A2:A10) as well as CHOOSE({2,1},A2:A10,B2:B10) presents the values of column B first to the VLOOKUP function, and thus, using 2 as column index only tells the VLOOKUP function to return the value from the second element of each array combination

[Rick Rothstein]

1. There is no need to reverse the order of the array elements in the first argument, provided you've used the required order in the subsequent arguments
2. CHOOSE({1,2},B2:B10,A2:A10) as well as CHOOSE({2,1},A2:A10,B2:B10) presents the values of column B first to the VLOOKUP function, and thus, using 2 as column index only tells the VLOOKUP function to return the value from the second element of each array combination
Okay, so we are both saying the same thing. The only reason I commented in the first place is presenting the first array argument to CHOOSE as {2,1} just looked "odd" and "unnatural" to me (but you have to remember, I tendencies to be Obsessive-Compulsive, so things like that "bother" me more than it would most other people).

[Excel Fox]

And the only intent was to tell the reader that even though you pass the LOOKIN arrays in sequential order, you still can reverse its order by reversing the order of numbers in the first argument array of the CHOOSE function.

Which means that if you pass the LOOKIN array in the desired order already, you don't need to reverse the sequential numbers in the index argument of the CHOOSE function.

3rd point. CHOOSE({1,2},A:A,B:B) doesn't pass the element to VLOOKUP as A:B, rather, it passes a combination of multiple arrays having 2 element each, one from A and one from B. The VLOOKUP function actually looks up at these individual combination of arrays.

Which leads to the point that if one were to write VLOOKUP(LookupValue, CHOOSE({2,1},C1:C10,A1:B10),3,0), the formula would return a reference error, because CHOOSE (or Excel) isn't actually combining the array in to a range, but in to multiple arrays