Look left of the key in Google Sheets (INDEX/MATCH or XLOOKUP)

You can see sku 101 on the sheet and VLOOKUP still returns #N/A. The key is a real number. It is not missing. The formula is searching the name column. VLOOKUP cannot return a column to the left of the column it searches. INDEX/MATCH and XLOOKUP can.

Recreate it on a blank sheet

1. Open a blank spreadsheet. Leave the tab named Sheet1. In A1 type name, B1 sku, C1 price.
2. In A2 type Widget. B2 101 as a number. C2 9.5. E1 lookup. E2 101 as a number. F1 vlookup.
3. In F2 enter =VLOOKUP(E2,A2:C2,1,FALSE). The cell shows #N/A. The tooltip is Did not find value ‘101’ in VLOOKUP evaluation.

F2 selected. Formula bar shows =VLOOKUP(E2,A2,1,FALSE). F2 is #N/A. Tooltip: Did not find value '101' in VLOOKUP evaluation.

Leave F2 as the broken VLOOKUP.

4. That is not a missing key. Start the range at sku instead. G1 vlookup_b. In G2 enter =VLOOKUP(E2,B2:C2,1,FALSE). The cell shows 101. VLOOKUP can return the search column, or columns to its right. It cannot return name. Price sits to the right of sku, so a VLOOKUP that starts at B can return price. Name sits to the left. That is the wall.

Second recreate — price works, name does not. In G4 type vlookup_price. In G5 enter =VLOOKUP(E2,B2:C2,2,FALSE). The cell shows 9.5. Same table, same lookup value, range starting at sku. Index 2 is price. There is no index that returns name from B2:C2, and there is no index that finds 101 in A2:C2. Leave F2 #N/A next to G5 so the two outcomes stay on the sheet.

Do not move sku into column A to make F2 work. Leave the table as name | sku | price.

What is actually wrong

VLOOKUP always searches the first column of the range you give it. Range A2:C2 means it looks for 101 in A. A is Widget, not 101. Index 1 would return the name if it had found 101 in A. It never does. The #N/A is a search miss in the wrong column, not a missing row.

This is not a missing key. 101 is in B2 and in E2. See what #N/A means when the sku is actually absent (999). Changing E2 to 999 would also be #N/A, for a different reason. Put 101 back. If the error stays while the sku is visible in column B, you are on this page.

This is not number vs text, and not a trailing space. Both keys are numeric 101. ISNUMBER(B2) and ISNUMBER(E2) are TRUE. Those two mismatches are VLOOKUP no match.

This is not approximate match handing back the wrong row. F2 is #N/A, not a surprising name. The fourth argument is FALSE. The unsorted-TRUE trap is VLOOKUP wrong row.

This is not a column index past the range. Index 1 is legal on A2:C2. A bad index is #REF!. See VLOOKUP bad index.

Fix

Leave F2 as the broken VLOOKUP. Do not rearrange the table.

• H1 index_match. In H2 enter =INDEX(A2,MATCH(E2,B2,0)). The cell shows Widget.
• I1 xlookup. In I2 enter =XLOOKUP(E2,B2,A2). The cell shows Widget.

Both look up 101 in the sku column and return the name to the left. MATCH finds the row in B. INDEX returns the same row from A. XLOOKUP is the one-function form: lookup range B, result range A. For a taller table use B2:B100 and A2:A100, not a block A2:C100 as the lookup range.

I2 selected. Formula bar shows =XLOOKUP(E2,B2,A2). I2 is Widget. H2 is Widget from INDEX/MATCH. F2 is still #N/A. G2 is 101.

A constructed array =VLOOKUP(E2,{B2,A2},2,FALSE) can fake a left lookup by putting sku first inside { }. Do not do that as the everyday fix. Locale separators inside { } are their own parse error. See array literal locale. INDEX/MATCH and XLOOKUP name the columns directly.

The full migration off VLOOKUP (left lookup, default exact match, miss argument) is XLOOKUP vs VLOOKUP. This page is only the left-of-key #N/A.

If you need a column to the left of the key, do not rearrange the table to make VLOOKUP work. Use INDEX/MATCH or XLOOKUP.