The Beginners Guide on Reverse VLOOKUP in Excel

Have you ever had to reverse-lookup a value in Excel, pulling an answer from a column that sits to the left of the one you are searching?

Last updated: August 24, 2026

Whether you are looking up numbers or words, the four methods below all do the same job. Which one you should use comes down to which version of Excel you have open.

People call this a lot of different things: reverse VLOOKUP, VLOOKUP backwards, a backwards lookup, or simply the opposite of VLOOKUP. There is no separate reverse XLOOKUP function either, because XLOOKUP already searches in both directions on its own. Every one of those phrases describes the same job, and the fix is the same.

Read on as we cover the following:

• Why VLOOKUP cannot look left

• Four reverse lookup formulas, compared

• Method 1: XLOOKUP, the one-line answer

• Method 2: INDEX and MATCH

• Method 3: VLOOKUP with CHOOSE

• Method 4: Search bottom to top and return the last match

• Reverse lookup with multiple criteria

• Function syntax reference

• Troubleshooting

• Frequently asked questions

Why VLOOKUP cannot look left

The VLOOKUP function always searches the first column of whatever range you hand it, then counts columns to the right to find the answer. The third argument, col_index_num, is a count, and a count cannot be negative.

So if your ID sits in column A and the name you are searching by sits in column B, a plain VLOOKUP cannot give you the ID. People try =VLOOKUP(E2,A2:B10,-1,FALSE) and get #VALUE!, because a negative index is not something Excel accepts. There is no vlookup negative index, and there is no hidden setting for vlookup right to left.

Rule: A backwards VLOOKUP is not a different VLOOKUP. It is either a different function, or the same VLOOKUP handed a rearranged copy of your table.

That is the whole trick behind everything below. XLOOKUP does not care about direction. INDEX and MATCH do not care about direction. CHOOSE builds VLOOKUP a rearranged table so that the column it needs really is on the left.

Throughout this guide the examples use a small table where column A holds the name you want back, column B holds the city you are searching by, and cell E2 holds the city you are looking for. Swap the ranges for your own and the shape of every formula stays the same.

Four reverse lookup formulas, compared

Method 1: XLOOKUP, the one-line answer

XLOOKUP has no concept of left or right. You give it the column to search and the column to return, separately, and it does not care which order they sit in on the sheet.

=XLOOKUP(E2,B2:B10,A2:A10)

Read it as: find the value in E2 somewhere in B2:B10, and give me back whatever sits in the matching row of A2:A10. That is a complete reverse VLOOKUP in one line, with no array constants and no counting columns.

Two arguments worth adding straight away:

• =XLOOKUP(E2,B2:B10,A2:A10,"Not found") replaces the #N/A error with a message of your choosing.

• =XLOOKUP(E2,B2:B10,A2:A10,"Not found",0,-1) adds exact match and tells Excel to search from the bottom of the list upward, which returns the last matching row instead of the first.

XLOOKUP also defaults to an exact match, which is the opposite of VLOOKUP's default and removes an entire class of silent wrong answers. Microsoft's official XLOOKUP function reference documents all six arguments.

Rule: If XLOOKUP is available to you, no other method on this page is worth learning. Use the rest only when you have to hand the file to someone on an older build.

Method 2: INDEX and MATCH

This is the classic answer and it works in every version of Excel ever shipped. Two functions, nested.

=INDEX(A2:A10,MATCH(E2,B2:B10,0))

It works in two stages:

• MATCH(E2,B2:B10,0) finds the value in E2 inside the city column and returns its position in that range, as a plain number. If Charlotte is the fourth city in the list, MATCH returns 4. The final 0 means exact match.

• INDEX(A2:A10,4) then returns the fourth item from the name column.

MATCH goes inside INDEX as the row_num argument. Nothing is replaced and nothing is swapped. That nesting is the entire technique, and it is why direction is irrelevant: the two ranges are named separately, so either can be to the left of the other.

1. Start with INDEX and select the column you want the answer to come from, which here is the name column.

2. For the second argument, type MATCH, then the cell holding your search term and the column you want to search.

3. Add 0 as the MATCH type for an exact match, then close both brackets.

4. Press the Enter key. The formula returns the name that sits alongside the matching value in the city column.

Rule: Keep the INDEX range and the MATCH range the same height. If INDEX covers A2:A10 and MATCH covers B2:B11, every answer will be off by one row and nothing will look broken.

Method 3: VLOOKUP with CHOOSE

If you are stuck with VLOOKUP, you can hand it a rearranged copy of your table so that the column it needs to search really is the first one.

=VLOOKUP(E2,CHOOSE({1,2},B2:B10,A2:A10),2,FALSE)

The CHOOSE({1,2},B2:B10,A2:A10) part is doing all the work. The array constant {1,2} asks CHOOSE for both of its values at once, so instead of returning one column it returns a two-column virtual table with the city column first and the name column second. VLOOKUP then searches column 1 of that table, exactly as it always does, and returns column 2. The real sheet is never touched.

1. Type the VLOOKUP function and the lookup value, which is the cell holding the city you are searching for.

2. For the table_array argument, type the CHOOSE function, then the array constant and the two columns in the order you want them.

3. Put the search column first and the answer column second. Getting these the wrong way round is the single most common mistake with this method.

4. Finish with 2 for the column index and FALSE for an exact match, then press Enter.

Rule: In Excel 2019 and earlier this is an array formula. Confirm it with CTRL+SHIFT+ENTER rather than Enter, and Excel will wrap it in curly braces itself. In Microsoft 365 and Excel 2021, dynamic arrays handle it and plain Enter is enough. Skipping that step on an older build is why this formula seems not to work for so many people.

Method 4: Search bottom to top and return the last match

Every method above returns the first row that matches. If your city column has the same city three times and you want the most recent entry, you need to search in the opposite direction.

On Microsoft 365 or Excel 2021, add the search_mode argument:

=XLOOKUP(E2,B2:B10,A2:A10,"Not found",0,-1)

The -1 tells XLOOKUP to start at the bottom of the range and walk up, so the last match wins.

On any older version, use the LOOKUP trick:

=LOOKUP(2,1/(B2:B10=E2),A2:A10)

The expression 1/(B2:B10=E2) quietly turns every matching row into 1 and every non-matching row into a division error. LOOKUP then hunts for the largest value that is not greater than 2, skips the errors, and lands on the last 1 in the list. It looks strange and it is completely reliable.

Reverse lookup with multiple criteria

To match on city and department at the same time, multiply the two conditions together. A row where both are true evaluates to 1, and any row where either is false evaluates to 0.

=INDEX(A2:A10,MATCH(1,(B2:B10=E2)*(C2:C10=F2),0))

Or the modern equivalent:

=XLOOKUP(1,(B2:B10=E2)*(C2:C10=F2),A2:A10)

The INDEX and MATCH version is an array formula in Excel 2019 and earlier, so confirm it with CTRL+SHIFT+ENTER on those builds. Add a third condition by multiplying in another bracketed comparison.

Function syntax reference

The three screenshots below show the argument prompts as Excel displays them while you type.

VLOOKUP also supports wildcards in the lookup value for partial matches: * stands for any run of characters, ? stands for a single character, and ~ escapes either of them when you need to search for a literal asterisk or question mark. The dollar sign is not a wildcard, it locks a cell reference.

Want the full walkthrough of INDEX on its own? We cover every argument in the in-depth guide to the Excel INDEX function.

Troubleshooting

Reverse VLOOKUP in Google Sheets

Every formula on this page works unchanged in Google Sheets, including XLOOKUP, which Sheets added in 2022. The array-formula caveat does not apply, because Google Sheets handles the CHOOSE version without any special key combination.

Final thoughts on reverse VLOOKUP in Excel

Reverse lookup is worth learning because it removes the need to rebuild a table just to answer a question about it. Cross-referencing between two data sources stops being a copy-and-paste job.

Inventory work leans on reverse lookups constantly, for instance finding a SKU from a partial description, so our inventory spreadsheet template comes with the lookup columns already wired up.

Visit Simple Sheets for more easy-to-follow Excel guides, and remember to read the related articles section of this blog post.

For the most straightforward Excel video tutorials, subscribe to Simple Sheets on YouTube!

Frequently asked questions on reverse VLOOKUP in Excel

How do you do a reverse VLOOKUP in Excel?

Use =XLOOKUP(E2,B2:B10,A2:A10) if you have Microsoft 365 or Excel 2021. On any older version use =INDEX(A2:A10,MATCH(E2,B2:B10,0)). Both search the city column in B and return the matching name from column A, which is to its left.

Can VLOOKUP look backwards or to the left?

Not on its own. VLOOKUP always searches the first column of the range it is given and counts to the right, and col_index_num cannot be negative or zero. You can force it by rebuilding the range with CHOOSE, as in =VLOOKUP(E2,CHOOSE({1,2},B2:B10,A2:A10),2,FALSE).

What is the reverse VLOOKUP formula with CHOOSE?

=VLOOKUP(E2,CHOOSE({1,2},B2:B10,A2:A10),2,FALSE). CHOOSE with the array constant {1,2} builds a two-column virtual table with the search column first and the answer column second, so VLOOKUP can search it normally. In Excel 2019 and earlier, confirm it with CTRL+SHIFT+ENTER.

What is the opposite of VLOOKUP in Excel?

There is no single function called a reverse VLOOKUP. The practical opposites are XLOOKUP, which takes the search column and the return column as separate arguments, and the INDEX and MATCH pair, which does the same thing in every version of Excel.

Can I do a reverse lookup with multiple criteria in Excel?

Yes. Multiply the conditions together so that only rows matching all of them evaluate to 1, then look for that 1: =INDEX(A2:A10,MATCH(1,(B2:B10=E2)*(C2:C10=F2),0)). On Excel 2019 and earlier this is an array formula, so confirm it with CTRL+SHIFT+ENTER.

How do I make a reverse VLOOKUP return the last match instead of the first?

Add the search_mode argument to XLOOKUP: =XLOOKUP(E2,B2:B10,A2:A10,"Not found",0,-1). On older versions use =LOOKUP(2,1/(B2:B10=E2),A2:A10), which skips the non-matching rows and lands on the final match.

Are there any limitations or considerations when using reverse lookup in Excel?

The search column should hold unique values. If it contains duplicates, every standard lookup returns the first matching row rather than the one you had in mind. Watch for trailing spaces and for numbers stored as text, which are the usual cause of an unexpected #N/A.

How is a reverse lookup different from a regular lookup?

A regular lookup searches the leftmost column and returns something to its right. A reverse lookup searches a column further right and returns something to its left. The data is identical, only the direction of travel changes.

Related Articles

Doing Subtraction in Excel

Beginners Guide: How To Group Dates In Pivot Table

CHOOSECOLS Function In Excel : The Only Guide You Need