VLOOKUP has been the way to pull a value from one table into another for over thirty years, and its quirks are so familiar that people stop noticing
them: counting columns, remembering to type FALSE, rebuilding formulas after someone inserts a column. XLOOKUP arrived in
Excel for Microsoft 365 to fix exactly those things. Here is what actually changed, with the same lookup written both ways.
What are the differences between XLOOKUP and VLOOKUP?
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Default match | Approximate (needs FALSE for exact) | Exact |
| Which column it searches | Only the first column of the range | Any column or row you give it |
| Return a column to the left | No | Yes |
| How you pick the result | A column number (2, 3...) | A range, so inserted columns don't break it |
| Not found | #N/A, wrap in IFNA | Built-in if_not_found argument |
| Several columns at once | One formula per column | One formula, results spill across |
| Last match instead of first | No | Yes, search_mode -1 |
Wildcards * ? | On by default in exact mode | Only with match_mode 2 |
| Available in | Every version of Excel and Google Sheets | Excel 365, 2021, 2024, web; Google Sheets |
What does the same lookup look like in each?
Find the price of the product code in A2, in a table where codes are in column D and prices in column F:
=VLOOKUP(A2,$D$2:$F$200,3,FALSE) - A2: lookup_value
- $D$2:$F$200: table_array
- 3: col_index_num
- FALSE: range_lookup
=XLOOKUP(A2,$D$2:$D$200,$F$2:$F$200) - A2: lookup_value
- $D$2:$D$200: lookup_array
- $F$2:$F$200: return_array
The XLOOKUP version reads the way you think about the problem: find this, in this column, and give me that column. There is no column
number to count and no fourth argument to forget.
Why is XLOOKUP safer by default?
Leave the last argument off a VLOOKUP and it does an approximate match, which on unsorted data returns values from the wrong rows without
any error. It is the most damaging lookup mistake because nothing looks wrong. XLOOKUP defaults to an exact match, so forgetting an
argument gives you a correct answer or a clear #N/A. (More traps like this in VLOOKUP not working? 9 causes.)
Can XLOOKUP look to the left?
Yes. VLOOKUP searches down the first column of its range and can only count to the right:
XLOOKUP takes the search column and the return column as two separate ranges, so the result can sit on either side:
=XLOOKUP(A2,$F$2:$F$200,$D$2:$D$200) What happens when someone inserts a column?
VLOOKUP's 3 is just a number. Insert a column inside the table and the formula still returns column 3, which is now a
different column. XLOOKUP refers to the return range itself, so Excel adjusts it like any other reference and the formula keeps returning
the price.
How does each handle values that aren't found?
VLOOKUP needs a wrapper; XLOOKUP has a fourth argument for it:
=IFNA(VLOOKUP(A2,$D$2:$F$200,3,FALSE),"Not found") =XLOOKUP(A2,$D$2:$D$200,$F$2:$F$200,"Not found") - A2: lookup_value
- $D$2:$D$200: lookup_array
- $F$2:$F$200: return_array
- "Not found": if_not_found
Can XLOOKUP return several columns at once?
Yes. Give it a return range that is several columns wide and the results spill into the cells to the right. This brings back both the product name and the price with one formula:
=XLOOKUP(A2,$D$2:$D$200,$E$2:$F$200) How do you find the last match instead of the first?
Both functions return the first match by default. XLOOKUP's sixth argument, search_mode, can search from the bottom up
instead, which is how you get the most recent order for a customer in a list sorted by date:
=XLOOKUP(A2,$D$2:$D$500,$F$2:$F$500,"None",0,-1) - A2: lookup_value
- $D$2:$D$500: lookup_array
- $F$2:$F$500: return_array
- "None": if_not_found
- 0: match_mode
- -1: search_mode
Which is better for bands and grade boundaries?
For tax bands, commission rates or grades, VLOOKUP with TRUE works, but only if the first column is sorted in ascending
order. XLOOKUP with match_mode -1 (exact match or the next smaller value) gives the same result and does not need
the table sorted:
=XLOOKUP(B2,$H$2:$H$6,$I$2:$I$6,,-1) When should you still use VLOOKUP?
When the workbook has to open in Excel 2019 or earlier, which cannot calculate XLOOKUP. In that case, INDEX and MATCH give you most of XLOOKUP's advantages (left lookups, no column numbers) and work in every version:
=INDEX($F$2:$F$200,MATCH(A2,$D$2:$D$200,0)) And it is worth reading VLOOKUP fluently anyway: you will meet it in other people's workbooks for years. The same applies to HLOOKUP, which XLOOKUP also replaces. If your data runs across rows, the HLookup tool shows both formulas for your layout.
Try both on your own data
Paste your two tables into Lookup & Merge: it matches them, then shows the XLOOKUP, VLOOKUP and INDEX/MATCH formulas for your actual columns side by side, ready to copy into Excel. To practise writing them yourself, start
with XLOOKUP basics or VLOOKUP exact match.