Reverse Vlookup Using Choose Function

Printable View

03-21-2012, 11:06 PM
Excel Fox

Reverse Vlookup Using Choose Function

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/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
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=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
07-04-2013, 12:13 AM
Rick Rothstein

Quote:

Originally Posted by 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.

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
07-04-2013, 12:17 AM
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.
07-04-2013, 12:34 AM
Rick Rothstein

Quote:

Originally Posted by 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.

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).
07-04-2013, 12:50 AM
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)
07-04-2013, 01:05 AM
Rick Rothstein

Quote:

Originally Posted by 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)

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?
07-04-2013, 01:36 AM
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
07-04-2013, 01:45 AM
Rick Rothstein

Quote:

Originally Posted by 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

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).
07-04-2013, 01:50 AM
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

Country	Capital
India	New Delhi
Great Britain	London
Malaysia	Kuala Lumpur
China	Hong Kong