Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups

Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups

I previously posted this example of a reverse lookup, using the INDEX and MATCH functions.

The VLOOKUP function is commonly used with a table of data where the item being looked up is in a column to the left of the column holding the corresponding item you want to return, for example:



When you need to return a value from a column to the left of where your criteria is listed in the table, the VLOOKUP function can be used with an array constant (explained here), for example:
=VLOOKUP(I2,IF({1,0,0},C2:C13,A2:A13),3,0)



If your table has many columns between the lookup column and the corresponding item’s column, this formula from fellow Excel MVP Bob Umlas uses the more efficient CHOOSE function:
=VLOOKUP(I2,CHOOSE({1,2},G2:G13,A2:A13),2,0)

Here’s some info about why the formula with the array constant works.
ArrayConstant

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,
30 comments on “Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups
  1. Shah says:

    Thanks a lot, this was great however can you please help me out if I want to look up more than one value by just copying the formula and changing the reference without typing the entire formula again in other column.

    • Tom Urtis says:

      Well, you can, for example, enter other (in this tutorial’s case) names below Anne Teak, such as Dee Lited in cell I3, Sarah Bellum in cell I4, and so on, depending on who or what you want to separately look up. Then, the formula in cell J2 becomes this, with absolute and relative references:
      =VLOOKUP(I2,CHOOSE({1,2},G$2:G$13,A$2:A$13),2,0)

      If you want to run your lookups across a row (you said (“without typing the entire formula again in other column”), then you might have Dee Lited in cell K2, Sarah Bellum in cell M2, and so on. Then in cell J2 the formula becomes this, which you can copy to K2, M2, and so on:
      =VLOOKUP(I2,CHOOSE({1,2},$G2:$G13,$A2:$A13),2,0)

  2. Shah says:

    Hi Tom, Thank you so much I really appreciate your quick response. Actually what I wanted is, For Example I want to look up sales of Jim Nastik in column J2 then region in column K2 then State, years, calender & workers in columns L2, M2, N2 & O2 respectively without typing the entire formula again in new column and I will just copy the formula and change the column index num…please assist.

    • Tom Urtis says:

      I would think that for the scenario you describe, this variation would suffice, entered in cell I2 and copied across to O2:

      =VLOOKUP($I2,CHOOSE({1,2},$G2:$G13,A2:A13),2,0)

      The only change to the original formula I posted is the absolute column reference $ in front of I, and the two G’s.

  3. Shah says:

    thank you Tom, it works now.

  4. Colin says:

    Hi Tom

    I’ve seen the VLOOKUP + Choose combo before, and get the gist of how/why that works when CHOOSE({1,2}, drives the lookup value range (G$2:G$13) and the return value range (A$2:A$13) into the 2nd & 3rd arguments of the VLOOKUP function.

    However, I don’t understand how =VLOOKUP(I2,IF({1,0,0},C2:C13,A2:A13),3,0) works.

    Can you please add some explanation as to what the IF statement is doing, and how this drives the VLOOKUP to search in C2:C13 for the lookup value and return the corresponding value in A2:A13.

    • Tom Urtis says:

      Hi Colin, thanks for following my Excel blog. I modified this example today with a picture to help to explain how the formula works, in answer to your question.

  5. Gilmauro says:

    Wow,

    Hi Tom and thanks a lot!!!

    I was looking just for something just like a reverse vlookup and your tips were very useful

    Thanks again.

    Gil

  6. Venita says:

    Hi Tom – is there any function that i can use when I’m trying to find what’s missing from a table. For example, i have two days of data – when one is removed, i want to capture that. Day1 = 123, 234, 345, 456 and Day2 = 123, 345, 456, 789

    =IF(MATCH(A4,$B$1:$B$999,0),0,A4) = When the value is in the list, it returns 0 (which I want). But when it’s not in the list it gives me the #N/A.

    Is there a different formula I should use?

  7. Akhil says:

    Very helpful. Thanks….

  8. David says:

    What type of lookup would you do as sort of a spell-check? I have to enter in uniquely identified products numbers where I work. I have type them in from a sheet colleagues have hand-written from throughout the day. On occasion do to human error they necessary characters out. It’s not very easy to double check on the fly. I would love if I could type and then get a warning or some notification that what I entered doesn’t match anything. Save me a few steps on my corrections. Thanks!

  9. vikas says:

    Thanks a lot , Tom Urtis.
    Sir, would you please help me to know the way to get such brain/knowledge like you?

    Regards.

    awaiting response.

    • Tom Urtis says:

      Thank you very much. My own extensive knowledge of math, business and Excel is the result of my many years of education and experience in the business world. If you are asking how to become more knowledgeable in Excel I would recommend my books (you can search me on Amazon), and also keep learning something new about Excel every day.

  10. pankaj kumar says:

    how can putt up above one value in one time from reserve vlookup.

  11. Mike B says:

    Hi Tom

    Just came across this and found it very useful, learnt something new, but I was wondering why in your original example did you need 3 columns (1 of which duplicated), couldn’t you have just used?
    =VLOOKUP(I2,IF({1,0},C2:C13,A2:A13),2,0)

    Kind Regards

    Mike

  12. Sam G says:

    Hi Tom

    Your reverse vlookup formula works. However, is it possible to use this across multiple sheets(array is from multiple sheets)?

    • Tom Urtis says:

      It should be possible to use multiple sheets depending on your workbook’s design. The criteria cell (I2) can be on one sheet, and the two other columns can be qualified for their other parent worksheets whose names would precede those ranges in the formula. Did you test that and it did not work for you?

      • Sam G says:

        Thank you for your reply. I use the reverse vlookup formula with the Choose function and it is working between two sheets but dont know how to change the formula to include a third sheet. Basically, sheet 1 is my main sheet and I need to retrieve the same type of information (I got from the second sheet-i.e. Company Names) from the third sheet. My concern now is I want to extend the formula I have in the main sheet(Sheet 1) to include Sheet 3 to be able to retrieve the said information.

  13. SassySezzy says:

    Please help me I have an inventory (table) Column A has the Date. Column B has the Product code(unique). Column c has the number of products still available. The date is not sorted – I need a formula that will look for product code and get the count of the LATEST date.

  14. Bhavesh Alur says:

    Can Choose function be used for multiple columns for Vlookup ? Choose either from {1,2,3} ?

    • Tom Urtis says:

      Not sure I understand. CHOOSE can be used for any specified column. It sounds like when you say “Choose either from {1,2,3}” you want the user to indicate which column to look in. Why would that be the case if the formula should be written based on how the worksheet is known to be arranged?

  15. Prasad Waghmare says:

    but sir I have to work on 2 different excel sheet then how I use reserve vlookup formula.

    please guide me.

  16. mahesh says:

    can we work reverse look up between different work sheets

Leave a Reply

Your email address will not be published. Required fields are marked *

*