Analistable

VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?

By the Analistable team · Updated · 5 min read

Use XLOOKUP if everyone opening the file has Excel 365, Excel 2021 or Google Sheets — it looks left, defaults to exact match and handles missing values. Use INDEX MATCH when the file must work in older Excel versions. Use VLOOKUP only for simple lookups in legacy files.

Part of our guide: How to join spreadsheets on a common column

Side-by-side comparison

We compared the three functions on the questions that actually decide which one to use in a shared workbook.

Lookup functions compared
VLOOKUPINDEX MATCHXLOOKUP
Looks to the leftNoYesYes
Default match typeApproximateSet by you (0 = exact)Exact
Survives inserted columnsNoYesYes
Built-in 'not found' valueNo (wrap in IFNA)No (wrap in IFNA)Yes
Returns several columns at onceNoNoYes
Excel 2019 and olderYesYesNo
Google SheetsYesYesYes

VLOOKUP: the classic, with sharp edges

=VLOOKUP(A2, B:D, 3, FALSE) searches the first column of B:D and returns the third column. It works everywhere, but it can only look to the right, defaults to approximate match if you forget FALSE, and breaks when someone inserts a column because the index number is hard-coded.

INDEX MATCH: flexible and backward compatible

=INDEX(D:D, MATCH(A2, B:B, 0)) finds the row with MATCH and returns the value with INDEX. It can look left, survives inserted columns, and works in every Excel version — at the cost of a longer formula.

XLOOKUP: the modern default

=XLOOKUP(A2, B:B, D:D, "Not found") does what INDEX MATCH does in one function, with exact match by default and a built-in fallback for missing values. It can also search from the bottom and return several columns at once.

  • Available in Excel 365, Excel 2021 and later, Excel for the web, and Google Sheets.
  • Not available in Excel 2019 or earlier — recipients see #NAME?.

When a lookup isn't enough

All three functions return a single match. In the example below, every formula pulls 120 for Bakery Lune — the first order — although the customer actually spent 200 across two orders.

With real data the gap grows: if one customer has twenty orders, you still get only the first. For totals per customer, matches across several sheets, or data split between Google Sheets and Excel, a join is the right tool — see our guide to joining two spreadsheets on a common column.

Looking up Bakery Lune's order amount (C-101 has orders of €120 and €80)
ApproachFormulaResult
VLOOKUP=VLOOKUP("C-101", Orders!B:C, 2, FALSE)120
INDEX MATCH=INDEX(Orders!C:C, MATCH("C-101", Orders!B:B, 0))120
XLOOKUP=XLOOKUP("C-101", Orders!B:B, Orders!C:C)120
SUMIFS (aggregate)=SUMIFS(Orders!C:C, Orders!B:B, "C-101")200
Join + group byCustomers ⟕ Orders, SUM(amount)200

Approximate match: tiers and price bands

Approximate match is useful for banded tables such as discount tiers, where you want the row for the largest threshold that doesn't exceed the value. Each function handles it differently:

VLOOKUP:      =VLOOKUP(B2, Tiers!A:B, 2, TRUE)
INDEX MATCH:  =INDEX(Tiers!B:B, MATCH(B2, Tiers!A:A, 1))
XLOOKUP:      =XLOOKUP(B2, Tiers!A:A, Tiers!B:B, , -1)

VLOOKUP with TRUE and MATCH with 1 both require the thresholds sorted in ascending order; on unsorted data they return wrong answers without any error. XLOOKUP's match mode -1 (exact match or next smaller item) works on unsorted data, which removes a common source of silent mistakes.

Discount tiers looked up for an order of 750
ThresholdDiscountSelected?
00%
5005%Yes — largest threshold ≤ 750
100010%

Looking up on two criteria

VLOOKUP can only match one column, so the usual workaround is a helper column that joins the two keys, such as =A2&"|"&B2, in both tables. INDEX MATCH and XLOOKUP can test both conditions directly with an array expression:

=INDEX(Stock!C:C, MATCH(1, (Stock!A:A=E2) * (Stock!B:B=F2), 0))
=XLOOKUP(1, (Stock!A:A=E2) * (Stock!B:B=F2), Stock!C:C, "Not found")

In Excel 2019 and earlier, the INDEX MATCH version must be confirmed with Ctrl+Shift+Enter. Use bounded ranges such as A2:A5000 rather than whole columns for array expressions; whole-column arrays make the workbook noticeably slower.

Errors and what they mean

Common lookup errors
ErrorUsual causeFix
#N/ANo match, often from spaces or number-vs-text keysTRIM the key, match the data types, or add a not-found value
#REF!VLOOKUP column index larger than the rangeWiden the range or switch to INDEX MATCH or XLOOKUP
#NAME?XLOOKUP opened in Excel 2019 or earlierUse INDEX MATCH for files shared with older versions
Wrong value, no errorApproximate match on unsorted data, or a VLOOKUP missing FALSESet exact match explicitly
#SPILL!XLOOKUP returning several columns into occupied cellsClear the cells to the right

Google Sheets specifics

All three work in Google Sheets with the same core arguments, and VLOOKUP's last argument still defaults to approximate match there, so always pass FALSE. Sheets also offers FILTER and QUERY, which return every matching row rather than the first one — often a better fit than any lookup when a key repeats. For lookups between files, combine the lookup with IMPORTRANGE as described in linking two Google Sheets.

Switching an old workbook to XLOOKUP

Before replacing VLOOKUPs, check who opens the file: anyone on Excel 2019 or earlier will see #NAME? once the workbook recalculates. If everyone has Excel 365, Excel 2021 or later, convert one column at a time and compare the old and new results side by side with =A2=B2, then filter that check column for FALSE, before deleting the old formula. Keep a copy of the original file until the comparison is clean. VLOOKUPs that relied on approximate match by accident are where results usually change, and those differences are exactly the ones worth investigating.

Frequently asked questions

Is XLOOKUP faster than VLOOKUP?
For typical sheets the difference is negligible. Choose based on compatibility and readability rather than speed.
Does XLOOKUP work in Google Sheets?
Yes, Google Sheets supports XLOOKUP with the same arguments as Excel.
Can these functions return multiple matches?
No — each returns the first match. Use FILTER, a pivot table or a join when you need every matching row.
Can XLOOKUP find the last match instead of the first?
Yes. Set the sixth argument, search mode, to -1 to search from the bottom up, which returns the last matching row, for example a customer's most recent order.

Related guides