XLOOKUP does everything VLOOKUP does, with fewer ways to go wrong: it matches exactly by default, takes the return column as a range instead of a number, can look to the left and has its own "not found" argument. Use VLOOKUP when the file has to work in Excel 2019 or older, where XLOOKUP does not exist. The sheet below runs the same lookup both ways.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Pear | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | XLOOKUP | $1.50 | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Both return $1.50. Click each formula: VLOOKUP outlines the whole table A2:D6 and counts 3 columns into it; XLOOKUP outlines only the column it searches and the column it returns.
VLOOKUP vs XLOOKUP comparison
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Excel versions | All | Excel 2021, 2024, Microsoft 365, web |
| Default match | Approximate (if the 4th argument is left out) | Exact |
| Return column | A number counted into the table | A range |
| Inserting a column inside the table | Returns the wrong column | Keeps working |
| Look to the left | No | Yes |
| Value not found | #N/A, wrap in IFNA | 4th argument, "Not found" |
| Last match | No | search_mode -1 |
| Several columns at once | One per formula (or {2,3} as the column number in Microsoft 365) | A return range of several columns spills them |
| Approximate match | Next smaller, data must be sorted | Next smaller or next larger, any order |
| Wildcards | On with FALSE | Only with match_mode 2 |
| Horizontal lookup | Needs HLOOKUP | Same function |
Both ignore case. With an exact match both return the first match from the top, unless XLOOKUP is told to search from the bottom.
Not found: IFNA vs the fourth argument
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Kiwi | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | #N/A | |
| 3 | Pear | Fruit | $1.50 | 25 | with IFNA | Not found | |
| 4 | Carrot | Vegetable | $0.80 | 60 | XLOOKUP | Not found | |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A The value you looked for is not in the lookup range.Plain VLOOKUP shows #N/A. VLOOKUP needs an IFNA wrapper to show text, which XLOOKUP does with its fourth argument. Type Milk in G1 and all three agree: $1.10.
Looking left and moving columns
VLOOKUP's two structural limits come from the column number. It can only count to the right of the column it searches, and the number does not change when the table does: insert a column between Category and Price, and =VLOOKUP(G1,A2:D6,3,FALSE) still returns column 3, now the new column. XLOOKUP refers to the return column as a range, so Excel adjusts it like any other reference, and the search column can be anywhere.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | $0.80 | |
| 2 | Apple | Fruit | $1.20 | 40 | XLOOKUP | Carrot | |
| 3 | Pear | Fruit | $1.50 | 25 | INDEX MATCH | Carrot | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Both return Carrot. There is no VLOOKUP version: the name is to the left of the price. In older Excel the answer is INDEX MATCH, shown in G3, which also survives inserted columns. The INDEX and MATCH page explains it.
Approximate match, both ways
For bands, VLOOKUP uses TRUE and needs the first column sorted ascending. XLOOKUP uses match_mode -1 and does not need sorting, and match_mode 1 gives the next larger value instead, which VLOOKUP cannot do.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | 4,200 | |
| 2 | 0 | 0% | VLOOKUP | 3% | |
| 3 | 1000 | 3% | XLOOKUP | 3% | |
| 4 | 5000 | 5% | |||
| 5 | 10000 | 8% |
Both return 3% for 4,200. Change E1 to 10000 and both return 8%.
When to keep VLOOKUP
- The file is shared with people on Excel 2019, 2016 or older. XLOOKUP shows #NAME? there. INDEX MATCH is the other choice that works everywhere.
- The workbook already has hundreds of VLOOKUPs that work. Rewriting them gains little; use XLOOKUP for new formulas.
Speed is not a reason to pick either. On ordinary sheets both are instant, and on very large sorted lists XLOOKUP's binary search (search_mode 2) and VLOOKUP with TRUE are both fast.
Google Sheets supports both functions with the same arguments.
Practice: rewrite a VLOOKUP
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | 40 | Stock | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Your turn: =VLOOKUP(G1,A2:D6,4,FALSE) returns the stock of the product in G1. In G2, write the same lookup with XLOOKUP.
Frequently Asked Questions
Is XLOOKUP better than VLOOKUP?
For new work in Excel 2021 or Microsoft 365, yes: it defaults to an exact match, has no column number that breaks when columns move, looks left, and has built-in not-found text. VLOOKUP is better only when the file must work in Excel 2019 or older, where XLOOKUP shows #NAME?.
Is XLOOKUP faster than VLOOKUP?
Not in a way you will notice on ordinary sheets; both search a few thousand rows instantly. On very large sorted data, XLOOKUP's binary search mode (search_mode 2) is faster than a linear search, and VLOOKUP with TRUE also searches in binary.
What is the difference between XLOOKUP and INDEX MATCH?
They can do the same lookups. XLOOKUP is one function with simpler arguments and a not-found argument; INDEX MATCH works in every Excel version. =XLOOKUP(F2,A2:A6,C2:C6) and =INDEX(C2:C6,MATCH(F2,A2:A6,0)) return the same value.
How do I convert a VLOOKUP to XLOOKUP?
Keep the lookup value, split the table into the first column and the column you returned, and drop the column number and FALSE: =VLOOKUP(F2,A2:D6,3,FALSE) becomes =XLOOKUP(F2,A2:A6,C2:C6).
