Reverse VLOOKUP in Excel (Right to Left)

1 Reverse VLOOKUP with XLOOKUP Recommended

XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP, and it handles a reverse lookup natively — no tricks, no helper columns. It can search a range and return a value from any direction (left, right, up, or down), which makes it the simplest way to do a VLOOKUP to the left.

Syntax

=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 Excel will search for the value.
• return_array — the range from which Excel returns the matching value (this can sit to the left of the lookup_array — that's what makes it a reverse lookup).
• [if_not_found] — the value to return if no match is found. Optional.
• [match_mode] — 0 for exact match, -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard. Optional, defaults to 0.
• [search_mode] — 1 for first-to-last, -1 for last-to-first. Optional, defaults to 1.

Example

In the example below, you have a list of Employee Names in column A and Employee IDs in column B. You need to look up an Employee ID and return the matching Name from the column on the left.

Here's the formula:

=XLOOKUP(D2,B2:B11,A2:A11,"Not Found",0,1)

• D2 — the Employee ID you're searching for.
• B2:B11 — the lookup column (Employee IDs).
• A2:A11 — the return column (Names) — sitting to the left of the lookup column.
• "Not Found" — what to display if the ID doesn't exist.
• 0 — exact match.
• 1 — search from first to last.

How the formula works

XLOOKUP takes the value in cell D2 and searches for it inside the range B2:B11. The moment it finds a match, it returns the value from the same row position in A2:A11 — even though A2:A11 is to the left of the lookup column. If the ID isn't found, it shows "Not Found" instead of the standard #N/A error.

Unlike VLOOKUP, XLOOKUP doesn't care which column comes first. The lookup_array and return_array are independent — that's the entire reason it solves the reverse-lookup problem so cleanly.

Compatibility

When to use this method

Use XLOOKUP if you're on Excel 365, Excel 2021, or any newer version. It's the cleanest, fastest, and most readable way to do a reverse lookup. If you're on Excel 2019 or older and you see #NAME? when you type XLOOKUP, jump to Method 2 (INDEX-MATCH) instead.

2 Reverse VLOOKUP with INDEX and MATCH

Before XLOOKUP existed, the INDEX-MATCH combination was the standard solution for reverse lookups — and it still is for anyone using Excel 2019 or earlier. It works in every version of Excel, handles large datasets faster than VLOOKUP, and is more flexible than XLOOKUP in some edge cases.

The trick is simple once you see it:

MATCH tells INDEX the position (cell number) of a value in a column or row, and INDEX returns the value from that position.

Think of it this way — MATCH is the undercover agent who finds the criminal, and INDEX is the cop who arrests that criminal afterward.

Syntax

Here's the syntax of the INDEX function on its own:

=INDEX(array, row_num, [column_num])

• array — the column (or range) you want to return a value from.
• row_num — the row position of the value inside that array.
• [column_num] — the column position (only needed if the array has multiple columns). Optional.

The row_num argument is the missing piece. If you enter 4, INDEX returns the value from the 4th row of the array. The whole INDEX-MATCH trick comes down to one move:

Replace the row_num argument of INDEX with a MATCH formula.

MATCH looks up your value in the lookup column and returns its position number. INDEX then uses that position to pull the matching value from the return column — even when the return column is to the left of the lookup column.

Example

Below, you have a list of cities in column A and the names of employees working in those cities in column B. The goal is to look up the employee name working in Mumbai — meaning column A is the lookup column and column B is the return column.

Here's the formula:

=INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

• B2:B11 — the return column (employee names).
• E3 — the city you're looking up (Mumbai).
• A2:A11 — the lookup column (cities).
• 0 — exact match.

How the formula works

Let's break the formula into two parts.

Part 1 — MATCH finds the position The MATCH function looks for the value "Mumbai" inside the cities column (A2:A11) and returns 5 — because Mumbai is in the 5th cell of that column.

Part 2 — INDEX returns the value INDEX now knows it needs to return the value from the 5th cell of the employee names column (B2:B11). The 5th cell contains "Siya" — so that's the result.

That's the full mechanic — MATCH locates the row, INDEX delivers the value. Because the two functions are independent, the lookup column and the return column can sit in any order. That's what makes INDEX-MATCH a reverse VLOOKUP.

Compatibility

When to use this method

Use INDEX-MATCH if you're on Excel 2019 or older (where XLOOKUP doesn't exist), if you're sharing your file with people who might be on older versions, or if you're working with very large datasets where INDEX-MATCH typically outperforms VLOOKUP. It's the most universally compatible reverse-lookup method in Excel.

3 Reverse VLOOKUP with the LOOKUP function

The LOOKUP function is the oldest lookup function in Excel — older than VLOOKUP, HLOOKUP, and certainly XLOOKUP. It's also the simplest. Unlike VLOOKUP, LOOKUP doesn't care about column order, which means it can do a reverse lookup with no helper columns and no array tricks.

It comes with one important catch — and we'll cover it in detail in the "How it works" section.

Syntax

LOOKUP has two forms. For reverse lookups, you only need the vector form:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value — the value you want to search for.
• lookup_vector — a single row or column where Excel searches for the value.
• [result_vector] — a single row or column from which Excel returns the matching value. Can be on the left or right of the lookup_vector. Optional — if omitted, LOOKUP returns from the lookup_vector itself.

Example

Using the same dataset from Method 2 — cities in column A and employee names in column B — let's look up the employee working in Phoenix. Cities are the lookup column; names are the return column on the left.

Here's the formula:

=LOOKUP(E3,A2:A11,B2:B11)

• E3 — the city you're looking up (Mumbai).
• A2:A11 — the lookup_vector (cities).
• B2:B11 — the result_vector (employee names).

Notice there's no match-type argument — LOOKUP doesn't need one. The result is "Siya", the same answer you got with INDEX-MATCH and XLOOKUP.

How the formula works

LOOKUP searches the lookup_vector (A2:A11) for the value in E3. When it finds a match, it returns the value from the same position in the result_vector (B2:B11). Because the two vectors are independent ranges, the result column can sit anywhere — left, right, on a different sheet, even non-adjacent.

Important — LOOKUP assumes your lookup column is sorted in ascending order. If it isn't, LOOKUP can return the wrong value without showing any error. This is the single biggest reason INDEX-MATCH and XLOOKUP have largely replaced LOOKUP in modern Excel work.

For unsorted data, there's a workaround using a 1/(condition) pattern that forces an exact match:

=LOOKUP(2,1/(A2:A11=E3),B2:B11)

This formula always returns the correct value even when the data is unsorted, because the 1/(A2:A11=E3) trick creates an array of 1s and #DIV/0! errors, and LOOKUP then finds the last 1 — which is your match.

Compatibility

When to use this method

Use the standard LOOKUP form when your data is genuinely sorted in ascending order — for example, looking up a price band, a tax bracket, or a grade tier. Use the 1/(condition) trick when you need an exact match on unsorted data and you can't (or don't want to) use INDEX-MATCH or XLOOKUP. For most everyday reverse lookups, however, XLOOKUP or INDEX-MATCH are safer choices because they don't carry the sorting risk.

4 Reverse VLOOKUP using VLOOKUP with CHOOSE

This is the classic workaround for situations where you must use VLOOKUP — usually because you're working inside an existing template, following a company standard, or learning Excel before XLOOKUP existed. The CHOOSE function tricks VLOOKUP into thinking the columns are in a different order than they actually are, which lets it look up a value and return a result from the column on the left.

It's clever, it's old-school, and it works in every version of Excel.

Syntax

The pattern combines VLOOKUP with CHOOSE inside the table_array argument:

=VLOOKUP(lookup_value, CHOOSE({1,2}, lookup_column, return_column), 2, 0)

• lookup_value — the value you want to search for.
• CHOOSE({1,2}, lookup_column, return_column) — builds a virtual two-column array where the lookup column comes first and the return column comes second, regardless of where they actually live in your data.
• 2 — tells VLOOKUP to return the value from the second column of the virtual array (which is your return column).
• 0 — exact match.

Example

Using the same cities/employees dataset from Methods 2 and 3 — let's look up the employee working in Mumbai using VLOOKUP, even though the names column sits to the left of the cities column.

Here's the formula:

=VLOOKUP(D3,CHOOSE({1,2},A2:A12,B2:B12),2,0)

• E3 — the city you're looking up (Phoenix).
• CHOOSE({1,2},A2:A11,B2:B11) — virtually places the cities column (A2:A11) as column 1 and the names column (B2:B11) as column 2.
• 2 — return the value from the second virtual column (names).
• 0 — exact match.

The result is "David Wilson" — the same answer as the previous three methods, but achieved using VLOOKUP, which by itself can't look to the left.

How the formula works

VLOOKUP normally fails on a reverse lookup because it requires the lookup column to be the first column of the table_array. CHOOSE solves this by creating a brand-new two-column array in memory — Excel never actually sees your real data layout, just the rearranged version CHOOSE hands it.

The {1,2} argument is the key — it's an array constant that tells CHOOSE to return both values, in that order. So CHOOSE({1,2},A2:A11,B2:B11) returns a 10-row, 2-column virtual table where A2:A11 is column 1 and B2:B11 is column 2.

VLOOKUP then operates on that virtual table exactly as it would on a normal range — searches column 1 (cities), returns the matching value from column 2 (names). The reverse lookup happens silently inside CHOOSE, before VLOOKUP ever runs.

Quick check — in modern Excel (365 or 2021), this formula works as a regular formula. In Excel 2019 and earlier, you may need to enter it as an array formula by pressing Ctrl + Shift + Enter instead of just Enter. If you see #VALUE! in older Excel, that's almost always the cause.

Compatibility

When to use this method

Use VLOOKUP + CHOOSE when you're stuck with a template that already uses VLOOKUP and you can't switch to another function, when you're following a company standard that mandates VLOOKUP, or when you're teaching VLOOKUP and want to show students that even "left lookup" is possible without changing functions. For everyday work where you have a free choice, XLOOKUP or INDEX-MATCH are simpler and easier to read six months later.