Reverse VLOOKUP Example: Find Left in Excel

Reverse VLOOKUP means looking up a value that sits to the right of the column you want to return, going right-to-left instead of left-to-right. The standard VLOOKUP only searches the first column of a table, so a reverse lookup needs INDEX and MATCH (or XLOOKUP in newer Excel). This article shows a concrete reverse VLOOKUP example, explains how the formula works, and covers common pitfalls.

Formula

The reverse lookup formula is =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). The variables are:

• lookup_value – the text or number you are searching for (e.g., “Smith”).
• lookup_range – the column that contains that value (the column to the right of the return column).
• return_range – the column you want to pull data from (the column to the left).
• 0 – exact match, which is required for a reverse lookup.

In words: MATCH finds the row number where the lookup value appears in the lookup column, then INDEX returns the cell at that same row in the return column.

Worked Example

Suppose your data is in A1:B5, with employee names in column A and ID numbers in column B. You have an ID (say “E104”) and want the employee name. The lookup value is E104, the lookup range is B2:B5, and the return range is A2:A5. The formula is:

=INDEX(A2:A5, MATCH("E104", B2:B5, 0))

MATCH(“E104”, B2:B5, 0) returns 3 because E104 is the third cell in B2:B5. INDEX then returns the third cell in A2:A5, which is “Pat”. The result is “Pat”.

Why VLOOKUP Fails on Reverse Lookups

VLOOKUP always searches the first column of the table array. If your lookup column is to the right of the column you want to return, VLOOKUP cannot see it. For example, with columns A (name) and B (ID), VLOOKUP(“E104”, A2:B5, 1, FALSE) would try to find E104 in column A and fail. You could reorder the columns, but that often breaks other formulas or makes the sheet less readable. INDEX/MATCH avoids that.

Alternative: XLOOKUP (Excel 365 and 2021)

If you have Excel 365 or 2021, XLOOKUP is the modern replacement. The syntax is =XLOOKUP(lookup_value, lookup_range, return_range). For the example above: =XLOOKUP("E104", B2:B5, A2:A5). XLOOKUP defaults to exact match and works in any direction. It also handles errors more gracefully with its optional if-not-found argument. If your version supports it, use XLOOKUP instead of INDEX/MATCH.

Common Mistakes and How to Avoid Them

• Forgetting the exact match argument – MATCH defaults to approximate match if you omit the third argument. Always use 0 for exact match.
• Mismatched range sizes – return_range and lookup_range must have the same number of rows. If they differ, INDEX returns a wrong cell or an error.
• Mixing text and numbers – If you search for a number stored as text (e.g., “123” vs 123), MATCH won’t find it. Convert both to the same type.
• Trailing spaces – Extra spaces in the lookup value or in the data cause a no-match. Use TRIM() on both sides if needed.
• Using whole-column references – A2:A100000 is fine, but A:A can slow down the sheet. Use a defined range.

When to Use Reverse VLOOKUP vs Other Lookups

Use INDEX/MATCH (or XLOOKUP) when your lookup column is to the right of the return column. It is also useful when you add or remove columns, because INDEX/MATCH references the columns directly, not a table array position. If your table is small and the lookup column is already the first column, VLOOKUP is simpler. For two-way lookups (row and column), use INDEX/MATCH with two MATCH functions.

Frequently Asked Questions

Can I use VLOOKUP with a helper column to do a reverse lookup?

Yes. You can copy the lookup column to the left of the return column, or create a helper column that combines both columns. For example, add a column C that contains the ID, then VLOOKUP can search C and return A. But this duplicates data and can cause consistency issues, so INDEX/MATCH is cleaner.

Why does my reverse lookup return #N/A even though the value exists?

Common causes: the lookup value has extra spaces, the data is stored as text while your lookup value is a number (or vice versa), or the lookup range does not start on the same row as the return range. Check with TRIM() and ensure both ranges start at the same row.

Does XLOOKUP work in all Excel versions?

No. XLOOKUP is available in Excel 365 and Excel 2021, but not in Excel 2019 or earlier. For older versions, use INDEX/MATCH.

What if I need to return multiple columns in a reverse lookup?

Use a single MATCH to find the row, then INDEX with that row for each column. For example, =INDEX(A2:C5, MATCH("E104", B2:B5, 0), 1) returns the first column, ,2) the second, and so on. In Excel 365, XLOOKUP can return an entire row with =XLOOKUP("E104", B2:B5, A2:C5).

Is there a way to make the reverse lookup case-sensitive?

Use EXACT inside MATCH as an array formula: =INDEX(A2:A5, MATCH(TRUE, EXACT(B2:B5, "E104"), 0)). Enter with Ctrl+Shift+Enter in older Excel. In Excel 365, it works as a dynamic array formula without special entry.

Related

• Private-First Research Tools: Best Alternatives to AI Search
• Meta AI: What It Is and How Meta’s Chatbot Works
• Best RTS Games: Top Picks for Every Kind of Player

in t | Search value | - | Smith | 0
in d | Data range rows | - | 100 | 1
out pace | Match row | - | 1 | 0
out speed | Result | - | 1 | 0