How to Look Up to the Left in Excel

A VLOOKUP left lookup isn't possible: VLOOKUP searches the first column of a table and can only return columns to its right. To look up to the left, use INDEX MATCH, which works in every version of Excel, or XLOOKUP, which needs Excel 2021 or Microsoft 365. This guide shows both on the same data, plus a no-formula workaround, so you can pick the right one for your file or your job test.

Why VLOOKUP can't return a column to the left#

VLOOKUP's column number counts from the first column of the table, and it only counts to the right. Column 1 is the column it searches, column 2 is the next one to the right, and so on. There is no column 0 or column -1. A negative column number gives #VALUE!, and a 0 gives #VALUE! too.

Here is a cafe's stock list. You have a SKU and need the product name, which is to the left of the SKU:

To search the SKUs, VLOOKUP's table would have to start at column B, like B2:D9. Then column A is outside the table and VLOOKUP can't reach it. If you start the table at A instead, VLOOKUP searches the product names, not the SKUs, and returns #N/A. For a refresher on how VLOOKUP counts columns, see our beginner's guide to VLOOKUP.

Method 1: INDEX MATCH (every version of Excel)#

INDEX MATCH splits the lookup into two steps, so the search column and the return column can be anywhere. With the SKU in G2:

=INDEX($A$2:$A$9,MATCH(G2,$B$2:$B$9,0))

• MATCH(G2,$B$2:$B$9,0) finds the position of the SKU in the SKU column. For SY-045, that is 4.
• INDEX($A$2:$A$9, 4) returns the 4th product name: Vanilla Syrup.
• The 0 asks MATCH for an exact match. The $ signs lock both ranges so you can fill down with Ctrl+D (Mac: ⌘D).

Filled down G2:G6, the formula returns these names:

PK-399 isn't in the stock list, so MATCH can't find it and the result is #N/A. To show a message instead, wrap the whole formula in IFNA: =IFNA(INDEX($A$2:$A$9,MATCH(G2,$B$2:$B$9,0)),"Not found"). IFNA replaces only "not found" errors, so real mistakes still show.

Read it as: "Give me the product from the row where the SKU matches G2." Both ranges start on row 2 and end on row 9. That matters: if one starts on row 1 and the other on row 2, you get the wrong product with no error. The INDEX function guide explains the function on its own.

Method 2: XLOOKUP (Excel 2021 and Microsoft 365)#

XLOOKUP takes the search range and the return range separately, so "left" and "right" don't matter:

=XLOOKUP(G2,$B$2:$B$9,$A$2:$A$9,"Not found")

• G2 is what you know.
• $B$2:$B$9 is where to look.
• $A$2:$A$9 is what to bring back, to the left.
• "Not found" shows instead of #N/A when the SKU isn't in the list.

It is an exact match by default, so there is no FALSE or 0 to forget. The catch is the version: XLOOKUP isn't in Excel 2019 or earlier, where it shows #NAME?. See the XLOOKUP function guide for its other options, including partial-name searches covered in XLOOKUP wildcards and match modes.

Method 3: move the column (no new formulas)#

If you just need a quick answer, you can change the layout so VLOOKUP works. Cut column B (Ctrl+X, Mac: ⌘X), select column A, and right-click Insert Cut Cells. Now the SKU is the first column and the product is to its right:

=VLOOKUP(G2,$A$2:$D$9,2,FALSE)

This is fine for a one-off task, but it has costs. It changes a table other people may rely on, and the next time the data is exported in the old layout, your VLOOKUP breaks. You may also see tricks that rebuild the table inside VLOOKUP with CHOOSE. They work, but they are hard to read and check, so INDEX MATCH is the better habit.

Options for a VLOOKUP left lookup, compared#

All three give the same result on this data. Choose by version and how long the file will live.

In a job test, INDEX MATCH is a safe choice: it works in whatever Excel version the test uses, and it shows you understand how lookups work. It is a classic in Excel interview questions and part of the Excel skills data analysts need.

Common mistakes when looking left#

• Ranges that don't line up. =INDEX(A2:A9,MATCH(G2,B1:B9,0)) includes the header in MATCH, so every result is the product one row below the right one.
• Swapping XLOOKUP's ranges. The search range comes before the return range. Swapped, you search product names for a SKU and get #N/A.
• Forgetting MATCH's 0. Without it, MATCH does an approximate match on unsorted SKUs and can return the wrong row.
• Hidden spaces or numbers stored as text. These cause #N/A in any method. TRIM and VALUE fix most cases.

Left lookups use exact matches. If you need to place numbers into bands instead, such as commission tiers, see the VLOOKUP approximate match guide.

Practise it#

You'll remember the pattern after you have typed it a few times. ExcelReady's INDEX and MATCH lesson starts with a directory where the ID is left of the name, and builds the lookup one function at a time. The XLOOKUP lesson does the same left lookup on the cafe stock list above, with a "Not found" message for a missing SKU. Try the account exercise on this page first.