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.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | Monstera | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | Terracotta pot | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | Watering can | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | Fern | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | Potting mix | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | Monstera | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | Ceramic pot | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-107 | 1 | Snake 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.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | Monstera | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | Terracotta pot | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | Watering can | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | Fern | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | Potting mix | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | Monstera | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | Ceramic pot | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-107 | 1 | Snake 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).
| G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Product | Price | Stock | Product | Monstera | |
| 2 | PL-101 | Fern | $9.50 | 40 | SKU | PL-104 | |
| 3 | PL-104 | Monstera | $34.00 | 12 | |||
| 4 | PL-107 | Snake plant | $22.00 | 25 | |||
| 5 | PT-210 | Terracotta pot | $12.50 | 60 | |||
| 6 | PT-212 | Ceramic pot | $19.00 | 18 | |||
| 7 | TL-301 | Watering can | $18.00 | 9 | |||
| 8 | SL-401 | Potting mix | $7.25 | 80 |
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).
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | Grade | From | Grade | |
| 2 | Noor | A | 2 | 48 | F | 0 | F | |
| 3 | Mateo | B | 5 | 66 | C | 50 | D | |
| 4 | Hana | A | 8 | 81 | B | 60 | C | |
| 5 | Ollie | B | 3 | 55 | D | 70 | B | |
| 6 | Zara | A | 10 | 93 | A | 85 | A | |
| 7 | Ivan | B | 6 | 72 | B | |||
| 8 | Lea | A | 1 | 39 | F | |||
| 9 | Omar | B | 7 | 77 | B | |||
| 10 | Sara | A | 9 | 88 | A | |||
| 11 | Theo | B | 4 | 61 | C | |||
| 12 | Yuki | A | 6 | 70 | B | |||
| 13 | Bea | B | 8 | 84 | B |
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.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | Monstera | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | Terracotta pot | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | Watering can | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | Fern | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | Potting mix | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | Monstera | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | Ceramic pot | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-999 | 1 | Not 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
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | PL-101 | Fern | $9.50 | 40 | |||
| 3 | 2002 | PT-210 | 1 | PL-104 | Monstera | $34.00 | 12 | |||
| 4 | 2003 | TL-301 | 3 | PL-107 | Snake plant | $22.00 | 25 | |||
| 5 | 2004 | PL-101 | 1 | PT-210 | Terracotta pot | $12.50 | 60 | |||
| 6 | 2005 | SL-401 | 4 | PT-212 | Ceramic pot | $19.00 | 18 | |||
| 7 | 2006 | PL-104 | 1 | TL-301 | Watering can | $18.00 | 9 | |||
| 8 | 2007 | PT-212 | 2 | SL-401 | Potting mix | $7.25 | 80 | |||
| 9 | 2008 | PL-999 | 1 |
Common mistakes
Leaving off
FALSEin 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/Aor, 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.