Excel Reverse Lookup: Index Match Explained

Formulas & functions · Updated November 22, 2026

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. The 0 means 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 0 for exact match, but your data has duplicates, it returns the first match. Use MATCH(..., 0) to get the first occurrence. For the last occurrence, use MATCH(..., 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() or TEXT() 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.