Results 1 to 9 of 9

Thread: Reverse Vlookup Using Choose Function

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10

    Lightbulb 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
    Last edited by DocAElstein; 07-11-2023 at 12:56 PM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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
    Last edited by DocAElstein; 10-02-2023 at 12:43 PM.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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).

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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?

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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).

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 2
    Last Post: 04-16-2013, 01:36 PM
  2. Reverse name in excel with upper case,edit formula
    By shrinivasmj in forum Excel Help
    Replies: 3
    Last Post: 09-11-2012, 01:31 PM
  3. Choose from several sheets
    By marreco in forum Excel Help
    Replies: 3
    Last Post: 08-29-2012, 02:46 AM
  4. Replies: 4
    Last Post: 08-14-2012, 03:17 AM
  5. How to Get Comment by Vlookup Function :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 10-13-2011, 05:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •