Replace VLOOKUP with XLOOKUP in Google Sheets

VLOOKUP searches the first column of a block and, if you omit FALSE, approximates. XLOOKUP searches the range you name, defaults to exact match, and can return a column to the left of the key. This page is the migration. It is not a general #N/A primer — see what #N/A means if you still need that.

Recreate it on a blank sheet

1. Open a blank spreadsheet. In A1 type name, B1 sku, C1 price. In A2 type Widget. B2 101 as a number. C2 9.5. In A3 type Gadget. B3 102. C3 4. E1 lookup. E2 101 as a number. E3 999.
2. Left of the key. In F1 type vlookup_left. In F2 enter =VLOOKUP(E2,A2:C3,1,FALSE). The cell shows #N/A. VLOOKUP is searching column A (name) for 101. A is Widget. The sku is in B. This is the left-lookup trap; the full recreate lives on lookup left of key.
3. In G1 type xlookup_left. In G2 enter =XLOOKUP(E2,B2:B3,A2:A3). The cell shows Widget. The lookup range is sku. The result range is name. Name can sit on the left.

Leave F2 as the broken VLOOKUP.

Default match mode. In L1 type sku_u, M1 name_u. Type the skus out of order: L2 200, M2 Chair. L3 100, M3 Desk. L4 150, M4 Lamp. N1 find. N2 200. In O1 type vlookup_omit. In O2 enter =VLOOKUP(N2,L2:M4,2) with no fourth argument. The cell shows Lamp, not Chair. Omitting the fourth argument is approximate match (TRUE). The longer walkthrough is VLOOKUP wrong row. In P1 type xlookup_default. In P2 enter =XLOOKUP(N2,L2:L4,M2:M4). The cell shows Chair. No FALSE. Exact is the default (match_mode 0).

Missing key without wrapping IFNA. In J1 type vlookup_miss. In J2 enter =VLOOKUP(E3,B2:C3,2,FALSE). The cell shows #N/A because 999 is not in sku. That miss is expected; do not debug it as VLOOKUP no match (spaces / number-as-text) unless the sku is visibly on the sheet. In K1 type xlookup_miss. In K2 enter =XLOOKUP(E3,B2:B3,C2:C3,"none"). The cell shows none. The fourth argument is missing_value (Excel docs call the same slot if_not_found). It fires only when the lookup misses.

What is actually wrong

• VLOOKUP(range, index) always searches the first column of range. A name | sku | price table cannot return name with VLOOKUP unless you rearrange columns or build an array. XLOOKUP takes two ranges. The search column and the return column are independent.
• VLOOKUP without FALSE is approximate. XLOOKUP without a match mode is exact. Migrating and dropping FALSE because “XLOOKUP does not need it” is correct. Migrating and keeping a bare VLOOKUP is not.
• VLOOKUP has no miss argument. People wrap IFERROR and then hide #REF! from a bad index. XLOOKUP’s missing_value is the miss only, same idea as IFERROR vs IFNA. A broken range still errors.
• XLOOKUP does not fix a key that does not match. Number vs text and a trailing space still miss. That is still VLOOKUP no match, and XLOOKUP will #N/A for the same keys until you clean them. missing_value will not.

XLOOKUP lookup and result ranges must be one row or one column each, and the same size. A block like A2:C3 as the lookup range is the wrong shape. Name the sku column, then the price column.

Fix

Do not move sku into column A to make VLOOKUP happy. Leave F2 broken.

Replace the left lookup. Keep G2: =XLOOKUP(E2,B2:B3,A2:A3) returns Widget. The INDEX/MATCH form on the same layout is =INDEX(A2:A3,MATCH(E2,B2:B3,0)). Both are valid. XLOOKUP is the one-function version.

Replace the exact sku lookup. For price: =XLOOKUP(E2,B2:B3,C2:C3) returns 9.5. That is the VLOOKUP(E2,B2:C3,2,FALSE) replacement. You drop the column index. Inserting a column between sku and price does not silently shift the return.

Replace the IFNA(VLOOKUP(...),"none") wrap. =XLOOKUP(E3,B2:B3,C2:C3,"none") returns none. Use a fallback only when a miss is a valid business result. If 999 should have been on the table, put it on the table. Do not paper over it.

Approximate match, when you actually want a band (tax bracket, quantity break), is match_mode -1 or 1 on XLOOKUP, not “forget FALSE on VLOOKUP.” Do not sort the sku table to make TRUE look right.

If the sku is on the sheet and both VLOOKUP and XLOOKUP say #N/A, the key does not match. Fix type and spaces first. Then migrate.