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.
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.
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)
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.
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.
thank you Tom, it works now.
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.
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.
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
Hi Gil, thank you for the nice comment, and thanks for visiting my Excel blog page!
-Tom
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?
Hmmmm…Not sure about your worksheet layout, but see if this link on my blog helps you…
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-comparing-two-lists-and-formatting-differences/
…or maybe you need one of these formulas depending on the issue:
Is the value in list in column B also in list in column A? =IF(COUNTIF($A$1:$A$100,B1),”In both lists”,”In B only”)
Lists in ColA and ColB. Count of times cells in A & B on same row = value in C1. =SUMPRODUCT((A1:A10=C1)*(B1:B10=C1))
Are two lists identical? Test for TRUE (identical) or FALSE (different). Array (Ctrl+Shift+Enter) =AND(A1:A50=B1:B50)
Very helpful. Thanks….
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!
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.
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.
how can putt up above one value in one time from reserve vlookup.
Please describe an example.
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
Hi Mike, thanks for your comment. Not sure I follow you. Where is a column duplicated in my example?
Hi Tom
Your reverse vlookup formula works. However, is it possible to use this across multiple sheets(array is from multiple sheets)?
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?
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.
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.
See if the concept for that, as I show here, can be modified for your situation.
https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-lookup-oldest-and-newest-date/
Can Choose function be used for multiple columns for Vlookup ? Choose either from {1,2,3} ?
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?
but sir I have to work on 2 different excel sheet then how I use reserve vlookup formula.
please guide me.
Please explain your sheet tab names and where the data is located so we can provide a suggestion that is relevant to what you are working with.
can we work reverse look up between different work sheets
Maybe, but your question can have a lot of variations that you would need to explain.