Let’s say you manage a retail store and have two lists: one with product IDs and prices, and another with product IDs and names. You need to find the product names using the prices. Here, a reverse VLOOKUP can help.
In Excel, there are two ways to perform a reverse VLOOKUP, one is by using the a combination of INDEX and MATCH and the second is by using a new function call XLOOKUP.
Reverse VLOOKUP with INDEX-MATCH
To understand the working of INDEX and MATCH as a reverse VLOOKUP formula, a simple thing you need to pick:
Match tells the index the position (cell number) of a value in a column or in a row, and then, the index returns that value using that position (cell number).
Just think like this, the MATCH function is an undercover agent who finds the criminal and the INDEX function is a cop who arrests that criminal afterward.
But, let’s learn in detail how we can combine these two functions. Below is the syntax of INDEX, as you know.
INDEX(array, row_num, [column_num])
In the INDEX function, the row_num argument tells it, that from which row it has to return the value. Let’s say if you enter 4 it will return the value from the 4th row.
To create the reverse VLOOKUP formula we need to replace MATCH with row_argument of INDEX.
When we use MATCH, it looks up the value from the lookup column and returns the cell number of that value. And then, INDEX uses that number to figure out the position of the cell from the value column.
In the end, it returns the value from that cell and you get the value you are looking for. But now, let’s work with a real example. Below we have a list of cities and names of the employees who are working there.
Here we need to look up the employee name who’s working in Mumbai. Now, if look at the data, in the column where you have cities, that is our lookup column, and in the column where you have employees’ names, that is your value column. And, the formula will be:
=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)
Let’s break down this formula into two parts to understand it.
Part 1: In part one, we used the match function to look up the value “Mumbai” and it returned the “5” which is the position of the cell in which you have the value “Mumbai” in the cities column.
Part 2: In part two, we used INDEX and referred to the employee’s name column for the lookup of the value. Here index function knows that you want the value from the 5th cell from the column. So, it has returned “Siya” in the result.
Reverse VLOOKUP with XLOOKUP
XLOOKUP is relatively a new function, or you can say it’s an improved version of VLOOKUP and HLOOKUP. This function can lookup in any direction. It searches a range for a value and returns a corresponding value from another range, just like any other lookup function, it is way more flexible.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value you want to search for.
- lookup_array: The range where you want to search for the value.
- return_array: The range from which you want to return a value
- [if_not_found]: The value to return if the lookup value is not found. (Optional)
- [match_mode]: The type of match (0 for exact, -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard match). (Optional)
- [search_mode]: The search order (1 for first-to-last, -1 for last-to-first). (Optional)
In this example, we have a list of Employee IDs and Names. Now, you need to use Employee ID to get the name of the employee from a column which is on the left side.
=XLOOKUP(D2,B2:B11,A2:A11,"Not Found",0,1)
- D2: This is the Employee ID you are looking for in the lookup range (Emp ID Column).
- B2:B11: This is the column where it will search for the Employee ID.
- A2:A11: This is the name column from which it will return the name if it finds a match of Employee ID in B2:B11.
- “Not Found”: If the Employee ID in D2 is not found in B2:B11, it will return “Not Found”.
- 0: This means it will look for an exact match.
- 1: This tells function to search from the first to the last value.
In simple words, this formula looks for the Employee ID in D2 within the range B2:B11. If it finds it, it returns the Name from A2:A11. If it doesn’t find the value, it returns “Not Found”.
MY DATA LIKE
col1 col2 col3 col4
anyname1 indicator1 indicator2 indicator3
name2 MALE FEMLE TG Trance Gender
xyz 245 200 5
abc 520 450 10
tom 600 500 8
god 300 250 0
bob blankdata
output
Indocor1
male female
xyz ??? ???
i use this formula
=VLOOKUP($A2,Sheet1!$A$1:$E$13,MATCH(B$1,Sheet1!$A$1:$E$1,0),0)
=VLOOKUP(1stname,area,MATCH(2ndname,indicator,0),0)
but male female data not in my excel help me
Dear Puneet, can you help me please – it would be appreciated. I have tried to resolve this myself, without success using your examples as a base in INDEX & MATCH, and VLOOKUP.
I need a formula that will look at a cell containing a phrase (the phrase starts with a three letter code followed by other words), and then look specifically for the three letter code in that cell (using a list [range] of three letter codes for the search [14 three letter codes in the list]) and then provide me with the three letter code it has found from the list of codes.
My spreadsheet contains a column filled with these phrases, so I wish to insert a column next to the phrases in which the code can then be shown.
Dear Puneet, This is the most comprehensive article on I-M that I have read so far and have learnt many new ways to use them. Thanks a lot.
how to use index and match for multiple results, like if I am searching one customer name “Ram” who have bought the goods multiple time, I want the formula to search Ram and show results again and again till it reaches the last cell of the table.
Can you make this guide as a PDF so we can print it out and refer to it?
Hi Puneet,
I am looking for a formula based on Index+Match to return last record.
Thanks,
Paulo
Good stuff. I would recommend a different approach for 10. Multiple Criteria. Instead of using an array formula add column E as a helper column with the formula =A2&”|”&B2&”|”&C2. The index/match formula then matches on column E and thus no need for an array formula. I used “|” as my delimiter in this example any delimiter that will not be found in the data is fine.
Hi Puneet,
I am looking for a formula based on Index-Match to return last reord.
I have a file with PO numbers in Column-A, Invoice Nos-B, Invoice Dates-C and invoice amount-D in adjacent columns.
I need to search Last Invoice no., and Date for a particular PO number.
Super and amazing bro.. thumbs up .. i was waiting for such things.. i got it now..
Please, there is an omission in section 14: Create Hyperlink where you said “And, for this, we need to use HYPERLINK + Cell with INDEX and MATCH and the formula will be:?
So, what is the missing formula
When do you use index and when offset? What is the performance difference?
Sir,
Excellent work in excel vba…. I am one of the daily visitor to your website. I am proud to say, I have learned more and more in excel vba from ExcelChamps.
Please explain the following both functions done in one column in excel.
1.How to rounded off the numbers i.e (18.5 to 18.9) to next number i.e (19) using
VBA.
2.How to rounded off the numbers i.e (18.1 to 18.4) to the main number i.e (18) using
VBA.
Best Regards,
YLNV Prasad Rao
Yep, got ’em finally! I can’t thank you enough Puneet!
Bikash
Hi Puneet,
I’ve been following your posts daily and am a great admirer of your work. Sadly, I couldn’t download the Sample File you tagged with your article yesterday named “How to use INDEX and MATCH in Excel – The Everything Guide”. Perhaps you already got the same feedback from your other readers. Could you please e_mail me the Sample File? Or, you can also fix the glitch if there’s any. Obviously, I’ll get back to your site again tomorrow and I can check then. Thanks much!
Bikash
Hey Bikash,
I’m sad you had to face this problem. It was due to some conflict between the site and the tool I use here to serve files. .It’s sorted now you can download the files from the link I have shared above.
I have sent one mail Please rectify and revert me
I really like your guides on EXcel, maybe I’m expecting more, but many times lately I’ve found the “Sample Files” (just above Conclusion) doesn’t work. I would think that this file would contain the images you use to generate this tutorial and thus have all these formulas and data?? Am I mistaken? I had the same basic problem with several lately. Also at the end of #14 on hyperlinks there is no formula after “and the formula will be:”
Hi Puneet…is there a formula to calculate the average of quartiles..I have used the quartile formula and obtained the values of the 4 quartiles.. now I need to know how to calculate the average of each quartile.. can you help?