formulastreak

VLOOKUP vs XLOOKUP

Updated · Works in: Excel 2021+, Microsoft 365, Google Sheets · Every example checked before release

Short answer

Use XLOOKUP(key, lookup_range, return_range, [if_not_found]) when everyone has Excel 2021, Microsoft 365 or Google Sheets: it can look left, defaults to an exact match and has a built-in fallback. Use VLOOKUP(key, table, column, FALSE) (or INDEX/MATCH) for older Excel versions.

Both functions find a value in one table and bring back related data, like a product's price from its SKU. XLOOKUP is the newer, more flexible one; VLOOKUP still works everywhere. Every example below is a live sheet: edit the formula and the cells recalculate.

VLOOKUP in one minute

VLOOKUP(key, table, column_number, FALSE) searches the first column of the table for the key and returns the value from the column you name, counting from 1. The FALSE asks for an exact match. Lock the table with $ so it doesn't slide when you fill the formula down.

Orders and price list · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Monstera
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042MonsteraPL-101Fern$9.5040
32002PT-2101Terracotta potPL-104Monstera$34.0012
42003TL-3013Watering canPL-107Snake plant$22.0025
52004PL-1011FernPT-210Terracotta pot$12.5060
62005SL-4014Potting mixPT-212Ceramic pot$19.0018
72006PL-1041MonsteraTL-301Watering can$18.009
82007PT-2122Ceramic potSL-401Potting mix$7.2580
92008PL-1071Snake plant

The same lookup with XLOOKUP

XLOOKUP(key, lookup_range, return_range) names the column to search and the column to return separately. There's no column number to miscount, and exact match is the default, so there's no FALSE to forget.

Orders and price list · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Monstera
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042MonsteraPL-101Fern$9.5040
32002PT-2101Terracotta potPL-104Monstera$34.0012
42003TL-3013Watering canPL-107Snake plant$22.0025
52004PL-1011FernPT-210Terracotta pot$12.5060
62005SL-4014Potting mixPT-212Ceramic pot$19.0018
72006PL-1041MonsteraTL-301Watering can$18.009
82007PT-2122Ceramic potSL-401Potting mix$7.2580
92008PL-1071Snake plant

XLOOKUP can look left

VLOOKUP can only return columns to the right of the search column. Here the SKU sits left of the product name, so finding a SKU from a name needs XLOOKUP (or INDEX/MATCH).

Orders and price list · edit M2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultM2 = PL-104
GHIJKLM
1SKUProductPriceStockProductMonstera
2PL-101Fern$9.5040SKUPL-104
3PL-104Monstera$34.0012
4PL-107Snake plant$22.0025
5PT-210Terracotta pot$12.5060
6PT-212Ceramic pot$19.0018
7TL-301Watering can$18.009
8SL-401Potting mix$7.2580

Match modes: exact or the next smaller

XLOOKUP's 5th argument is the match mode: 0 exact (the default), -1 exact or the next smaller value, 1 exact or the next larger. That finds a grade or a commission tier. To skip the 4th argument, leave it empty between two commas: XLOOKUP(D2, $G$2:$G$6, $H$2:$H$6, , -1).

Exam results · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = F
ABCDEFGH
1StudentClassHoursScoreGradeFromGrade
2NoorA248F0F
3MateoB566C50D
4HanaA881B60C
5OllieB355D70B
6ZaraA1093A85A
7IvanB672B
8LeaA139F
9OmarB777B
10SaraA988A
11TheoB461C
12YukiA670B
13BeaB884B

A built-in fallback for missing keys

When a key isn't found, both functions return #N/A. XLOOKUP's fourth argument replaces that with anything you like. With VLOOKUP you'd wrap it: IFERROR(VLOOKUP(…), "Not found"). Try changing PL-999 to a real SKU.

Orders and price list · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Monstera
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042MonsteraPL-101Fern$9.5040
32002PT-2101Terracotta potPL-104Monstera$34.0012
42003TL-3013Watering canPL-107Snake plant$22.0025
52004PL-1011FernPT-210Terracotta pot$12.5060
62005SL-4014Potting mixPT-212Ceramic pot$19.0018
72006PL-1041MonsteraTL-301Watering can$18.009
82007PT-2122Ceramic potSL-401Potting mix$7.2580
92008PL-9991Not found

Check yourself

One real exercise from the “Lookups” unit. Checked on our server; no account needed.

Order 2008 has a SKU that isn't in the price list. Look up the Product again in D2, but show "Not found" instead of #N/A.

  • Use XLOOKUP
Select D2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042PL-101Fern$9.5040
32002PT-2101PL-104Monstera$34.0012
42003TL-3013PL-107Snake plant$22.0025
52004PL-1011PT-210Terracotta pot$12.5060
62005SL-4014PT-212Ceramic pot$19.0018
72006PL-1041TL-301Watering can$18.009
82007PT-2122SL-401Potting mix$7.2580
92008PL-9991

Common mistakes

  • Leaving off FALSE in VLOOKUP. It then assumes the first column is sorted and can return a wrong row without any error.

  • A stray space in a key (“TL-301 ”) makes an exact match fail with #N/A. Clean the key inside the lookup: VLOOKUP(TRIM(B2), …).

  • Forgetting $ on the table range, so lookups further down search a shifted table and return #N/A or, worse, a wrong value.

  • Hard-coding the column number in VLOOKUP, then inserting a column into the table. XLOOKUP's separate return range doesn't have this problem.

  • Sharing a file that uses XLOOKUP with someone on Excel 2019 or older: they'll see #NAME?.

Questions

Is XLOOKUP available in Google Sheets?+

Yes. Google Sheets has XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode), in the same order as Excel's.

Which Excel versions have XLOOKUP?+

Excel 2021, Excel 2024 and Microsoft 365 (including Excel for the web). Excel 2019 and older don't; use INDEX/MATCH or VLOOKUP there.

Is XLOOKUP slower than VLOOKUP?+

Not in any way you'd notice on normal sheets. Choose based on compatibility: XLOOKUP when everyone has a recent version, INDEX/MATCH when they might not.

Reading is step one. Now make it stick.

Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.

Practise it now