XLOOKUP vs VLOOKUP: what changed and which to use

Updated 30 October 2026

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?

VLOOKUPXLOOKUP
Default matchApproximate (needs FALSE for exact)Exact
Which column it searchesOnly the first column of the rangeAny column or row you give it
Return a column to the leftNoYes
How you pick the resultA column number (2, 3...)A range, so inserted columns don't break it
Not found#N/A, wrap in IFNABuilt-in if_not_found argument
Several columns at onceOne formula per columnOne formula, results spill across
Last match instead of firstNoYes, search_mode -1
Wildcards * ?On by default in exact modeOnly with match_mode 2
Available inEvery version of Excel and Google SheetsExcel 365, 2021, 2024, web; Google Sheets

What does the same lookup look like in each?

Find the price of P-103. VLOOKUP is given the whole table and counts across to the price column:

  • D2:F5 one table: searches its first column, then counts across to 3

=VLOOKUP("P-103", D2:F5, 3, FALSE) →2.25

XLOOKUP is given the column to search and the column to bring back, separately:

  • D2:D5 where to look
  • F2:F5 what to bring back

=XLOOKUP("P-103", D2:D5, F2:F5) →2.25

In a real sheet, with the code to find in A2 and the ranges locked so you can copy them down:

=VLOOKUP(A2,$D$2:$F$200,3,FALSE)
  • A2: lookup_value
  • $D$2:$F$200: table_array
  • 3: col_index_num
  • FALSE: range_lookup
✎ Search the first column of D:F, return the 3rd column, exact match.
=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
✎ Search D, return F. Exact match is the default.

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)
✎ Search the names in F, return the codes in D, to the left.

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. Here someone added a Colour column, and the price moved from column 3 to column 4:

  • E1:E5 a column someone inserted
  • F4 column 3 is now Product
  • G4 the price you wanted

=VLOOKUP("P-103", D2:G5, 3, FALSE) →Tape ✗

No error, just the wrong data. XLOOKUP refers to the return range itself, so when the price column moves from F to G, Excel moves the range with it and the formula keeps returning the price:

=XLOOKUP("P-103", D2:D5, G2:G5) →2.25

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:

  • E2:F5 two columns to bring back
  • H2:I2 one formula in H2, the answer spills into I2

=XLOOKUP("P-103", D2:D5, E2:F5)

In a real sheet, with the code in A2:

=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:

  • E2:F2 older P-103 price, skipped
  • F4 latest P-103 price

=XLOOKUP("P-103", E2:E5, F2:F5, "None", 0, -1) →2.25

In a real sheet, with the code to find in A2:

=XLOOKUP(A2,$E$2:$E$500,$F$2:$F$500,"None",0,-1)
  • A2: lookup_value
  • $E$2:$E$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:

  • H2:H5 the lowest score for each grade
  • H4:I4 64 is at least 60 but under 70

=XLOOKUP(64, H2:H5, I2:I5, , -1) →B

In a real sheet, with each score in B2:

=XLOOKUP(B2,$H$2:$H$5,$I$2:$I$5,,-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))

INDEX MATCH in Excel explains how the two parts fit together, including lookups on two or more criteria.

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.

Revision Q&A

(click a question) Guide Q&A
Q1: Is XLOOKUP better than VLOOKUP?

For new work, yes. XLOOKUP matches exactly by default, can look left, does not break when columns are inserted, and has a built-in not-found argument. VLOOKUP is still worth knowing because millions of existing workbooks use it.

Q2: Which versions of Excel have XLOOKUP?

Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Excel 2019, 2016 and earlier do not have it. Google Sheets has had XLOOKUP since 2022.

Q3: What happens if I send a file with XLOOKUP to someone on Excel 2019?

Their Excel cannot calculate XLOOKUP. The last saved results may show at first, but the formulas turn into #NAME? errors as soon as the sheet recalculates. Use INDEX and MATCH if the file has to work in older versions.

Q4: Is XLOOKUP faster than VLOOKUP?

On everyday sheets you will not notice a difference. On very large tables sorted by the key, XLOOKUP with search_mode 2 uses a binary search, which is much faster than scanning every row.

Q5: Can XLOOKUP look up with two criteria?

Yes. Multiply the conditions to build a column of 1s and 0s and look up 1: =XLOOKUP(1,(D2:D200=A2)*(E2:E200=B2),F2:F200). Each condition is a TRUE or FALSE test, and multiplying them means both must be true.

Q6: Are XLOOKUP and VLOOKUP case-sensitive?

No. Both treat "smith" and "SMITH" as the same value. For a case-sensitive lookup, use EXACT inside the lookup: =XLOOKUP(TRUE,EXACT(D2:D200,A2),F2:F200).

Q7: Does XLOOKUP replace HLOOKUP too?

Yes. Give XLOOKUP a row instead of a column for the lookup and return arrays and it searches across, so one function covers VLOOKUP, HLOOKUP and most INDEX and MATCH uses.

Know someone fighting with a spreadsheet? Send them this page.