The direct answer: to perform a reverse lookup in Excel (find a value in a left column based on a value in a right column), use the formula =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). This combination works in any direction, unlike VLOOKUP, which only searches left to right.
Why VLOOKUP Fails at Reverse Lookups
VLOOKUP is the most popular lookup function, but it has a hard limitation: it can only return values from columns to the right of the lookup column. If your data has IDs in column B and names in column A, VLOOKUP cannot return the name for a given ID because the name is to the left. This is a common frustration for Excel users who inherit spreadsheets with non-standard layouts.
INDEX and MATCH solve this by separating the lookup and return ranges. MATCH finds the row (or column) position of the lookup value, and INDEX returns the value at that position in a separate range. This two-step process gives you full control over direction.
How INDEX and MATCH Work Together
Let’s break down the two functions:
- MATCH returns the relative position of a value in a range. For example,
=MATCH("A-123", B2:B100, 0)returns the row number (relative to B2) where “A-123” is found. The0means exact match. - INDEX returns the value at a given position in a range. For example,
=INDEX(A2:A100, 5)returns the 5th value in column A.
Combined: =INDEX(A2:A100, MATCH("A-123", B2:B100, 0)). This finds the row where “A-123” appears in column B, then returns the corresponding value from column A.
Step-by-Step Example with Real Data
Suppose you have a product list:
- A2:A20 contains product names (e.g., “Widget”)
- B2:B20 contains product codes (e.g., “W-101”)
You want to look up the product name for code “W-105”. In cell D2, enter:
=INDEX(A2:A20, MATCH("W-105", B2:B20, 0))
This returns “Gadget” if that is the name in the row where B contains “W-105”. The formula works because MATCH returns the row position (say 6) and INDEX pulls the 6th value from column A.
For a dynamic lookup, put the code in a cell, e.g., D1, and use =INDEX(A2:A20, MATCH(D1, B2:B20, 0)). This allows you to change the code in D1 and get a new result.
Choosing Between INDEX/MATCH and XLOOKUP
If you have Excel 2021 or Microsoft 365, XLOOKUP is often simpler: =XLOOKUP(D1, B2:B20, A2:A20). It does the same reverse lookup with one function. However, INDEX/MATCH still has advantages:
- Works in all Excel versions back to 2007.
- Faster on very large datasets (though XLOOKUP is close).
- More flexible for two-way lookups (row and column).
For older Excel versions, INDEX/MATCH is the standard solution. For new versions, XLOOKUP is easier to read and maintain. Choose based on your audience and compatibility needs.
Common Errors and How to Fix Them
Here are typical mistakes when using INDEX/MATCH:
- #N/A error: The lookup value is not found. Check for typos, extra spaces, or number vs. text formatting. Use
=IFERROR(your_formula, "Not found")to display a friendly message. - #VALUE! error: The ranges are different sizes. Ensure the MATCH range and INDEX range have the same number of rows.
- Wrong result: If MATCH uses
0for exact match, but your data has duplicates, it returns the first match. UseMATCH(..., 0)to get the first occurrence. For the last occurrence, useMATCH(..., 0)with a helper column or XLOOKUP. - Mismatched data types: If you lookup a number but the lookup column contains text (or vice versa), use
VALUE()orTEXT()to convert.
Advanced: Two-Way Lookup with INDEX/MATCH
INDEX/MATCH can also return a value at the intersection of a specific row and column. For example, with a table of sales by product (rows) and month (columns), you can find the sales for a product in a specific month:
=INDEX(B2:G20, MATCH("Widget", A2:A20, 0), MATCH("May", B1:G1, 0))
Here, the first MATCH finds the row for “Widget”, the second finds the column for “May”, and INDEX returns the cross cell. This is a powerful alternative to VLOOKUP with nested MATCH.
Frequently Asked Questions
Can I use INDEX/MATCH to lookup from right to left? Yes, that’s exactly what reverse lookup means. The MATCH range can be anywhere, and the INDEX range can be to the left of it. That’s the main advantage over VLOOKUP.
Is INDEX/MATCH faster than VLOOKUP? In most cases, yes, especially on large datasets. VLOOKUP scans the entire table, while MATCH can be optimized. However, the difference is negligible for small sheets.
Does INDEX/MATCH work with wildcards? Yes, you can use wildcards in the lookup value, like "*Widget*", but only with exact match (0). This finds the first cell containing “Widget”.
What if I want the last occurrence instead of the first? Use MATCH(1, (lookup_range=lookup_value), 0) as an array formula (Ctrl+Shift+Enter in older versions), or use XLOOKUP with the match_mode set to -1 for last, but that’s more complex. Simpler: sort your data in reverse order.
Can I use INDEX/MATCH with multiple criteria? Yes, by using array formulas or helper columns. For example, =INDEX(C2:C20, MATCH(1, (A2:A20="Widget")*(B2:B20="Red"), 0)) entered with Ctrl+Shift+Enter in older Excel, or with dynamic arrays in Excel 365.
Why do I get #N/A when the value exists? Often due to trailing spaces or number stored as text. Use =TRIM() on the lookup value and the lookup range, or convert text to numbers with --.
Related
- Free Excel Inventory Tracking Sheet Template? Yes—Here’s How
- Excel Inventory Tracker Template: How to Build One
- Excel Insert Yes No Checkbox: 2 Ways
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel Formula for Dates: Examples and FixesTo count cells that contain a particular date, use Excel’s COUNTIF function with a real date value as…
- Power Query XLOOKUP: Matching Data in Excel ExplainedPower Query does not have an XLOOKUP function. To match rows from two tables in Power Query, use…
- Excel Time Difference in Hours: Formulas and ExamplesTo calculate the difference between two Excel times in decimal hours, subtract the start time from the end…
- Excel Comparison Formulas: Functions and ExamplesExcel comparison formulas use operators such as =, <>, >, and <= to test values, usually returning TRUE…
- Formula for Overtime Hours in Excel: Simple ExamplesFor a daily overtime calculation in Excel, use =MAX(0,(MOD(C2-B2,1)-D2)*24-E2). With a start time in B2, end time in…
- Excel MEDIAN Function Examples and How It WorksTo find the middle value in Excel, use the MEDIAN function: with your numbers in B2:B8, enter =MEDIAN(B2:B8).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.