Expert guide

XLOOKUP vs INDEX MATCH vs VLOOKUP: Which to Use

Side-by-side formulas for the lookups you actually need

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: in Microsoft 365 and Excel 2021+, use XLOOKUP for almost everything: =XLOOKUP(value, lookup_range, return_range, "Not found"). It defaults to exact match, looks left or right, searches from the bottom for the last match, and returns several columns at once. Use INDEX/MATCH when the file must open correctly in Excel 2016/2019. Keep VLOOKUP only in old files that already work. For workbooks built to last, see our Excel consultants.

Lookups are the most used formulas in business spreadsheets, and the source of many silent errors: VLOOKUP returning the wrong column after someone inserted a column, approximate matches returning the nearest customer instead of #N/A, lookups that stop at row 5,000. Choosing the right function and pattern prevents most of them.

The Same Lookup Three Ways

Find the price (column D) of the product code in G2 from a table in A:D.

VLOOKUP:      =VLOOKUP(G2, A:D, 4, FALSE)
INDEX/MATCH:  =INDEX(D:D, MATCH(G2, A:A, 0))
XLOOKUP:      =XLOOKUP(G2, A:A, D:D, "Not found")
FeatureVLOOKUPINDEX/MATCHXLOOKUP
Default matchApproximate (dangerous)You choose (0 = exact)Exact
Look leftNoYesYes
Survives inserted columnsNo (column number)YesYes
If not found valueWrap in IFERROR/IFNAWrap in IFERROR/IFNABuilt in
Last matchNoComplexsearch_mode -1
Return several columnsOne per formulaOne per formulaYes, spills
VersionsAllAllMicrosoft 365, Excel 2021+

Patterns You Will Use Every Week

Left lookup (code from name)

=XLOOKUP(G2, C:C, A:A, "")
=INDEX(A:A, MATCH(G2, C:C, 0))

Two criteria (product and region)

=XLOOKUP(1, (tblPrices[Product]=G2) * (tblPrices[Region]=H2), tblPrices[Price], "None")
=INDEX(tblPrices[Price], MATCH(1, (tblPrices[Product]=G2) * (tblPrices[Region]=H2), 0))

Helper key alternative (fast, works everywhere):
Key column in table:  =[@Product] & "|" & [@Region]
Lookup:               =XLOOKUP(G2 & "|" & H2, tblPrices[Key], tblPrices[Price], "None")

Most recent record (last match)

Last order date for a customer (orders appended in date order):
=XLOOKUP(G2, tblOrders[Customer], tblOrders[OrderDate], "", 0, -1)

Order-independent version:
=MAXIFS(tblOrders[OrderDate], tblOrders[Customer], G2)

Return several columns at once

=XLOOKUP(G2, tblCustomers[ID], tblCustomers[[Name]:[Phone]])

One formula spills Name, Email and Phone into adjacent cells. Leave room to the right or Excel shows #SPILL!.

Approximate match for bands (tax, commission, shipping)

Band table sorted by lower limit (0, 1000, 5000, 10000):
=XLOOKUP(G2, tblBands[From], tblBands[Rate], , -1)        ' exact or next smaller
=INDEX(tblBands[Rate], MATCH(G2, tblBands[From], 1))       ' requires ascending sort

Two-way lookup (row and column)

=INDEX(B2:M20, MATCH(P2, A2:A20, 0), MATCH(Q2, B1:M1, 0))
=XLOOKUP(P2, A2:A20, XLOOKUP(Q2, B1:M1, B2:M20))

Why Lookups Return #N/A

  • Extra spaces or non-breaking spaces in either value.

  • Numbers stored as text on one side.

  • Different date or code formats (2026-01-05 vs 05/01/2026 as text).

  • The value genuinely does not exist.

Diagnosis steps are in fixing Excel formula errors; cleaning the source data in cleaning messy Excel data.

Performance Tips for Big Workbooks

  • Use Excel Tables or exact ranges instead of whole columns in very large workbooks.

  • Avoid repeating the same lookup in many columns: return several columns with one XLOOKUP, or look up a row number once with MATCH and reuse it with INDEX.

  • For hundreds of thousands of rows, consider Power Query merges or a data model instead of formulas.

  • Volatile functions (OFFSET, INDIRECT) around lookups force constant recalculation; avoid them.

Compatibility Decision

If everyone uses Microsoft 365, standardise on XLOOKUP. If a file goes to clients or colleagues on Excel 2016 or 2019, use INDEX/MATCH, or they will see #NAME?. Our custom spreadsheet work sets this standard at the start. For more modern functions, see FILTER, SORT and UNIQUE.

Migrating an Old Workbook From VLOOKUP

Replacing lookups in a live workbook is low-risk if you do it in a controlled way. Most of the value comes from fixing the lookups that break, not from rewriting every formula on principle.

  • Find lookups with approximate match: search for VLOOKUP formulas whose last argument is TRUE or missing. These are the dangerous ones; fix them first.

  • Find lookups with hard-coded column numbers into tables that change shape. Convert those to XLOOKUP or INDEX/MATCH so inserted columns no longer break them.

  • Convert source ranges to Excel Tables (Ctrl+T) so new rows are included without editing formulas.

  • Replace one sheet at a time and compare totals before and after with a simple check cell: =SUM(old column) - SUM(new column) should be 0.

  • Keep a copy of the original workbook until the next reporting cycle has run cleanly.

For large workbooks, an AI assistant can draft the replacement formulas from the originals if you paste the VLOOKUP and describe the table; check each one against the old result before switching over. The workflow is in our guide to using ChatGPT for Excel formulas.

FAQ

XLOOKUP vs INDEX MATCH FAQ

Choosing and using Excel lookup functions

For most lookups in Microsoft 365 or Excel 2021 and later, yes: XLOOKUP is shorter, defaults to exact match, has a built-in if-not-found value, can search from the bottom, and returns whole rows or columns. INDEX/MATCH remains useful when files must work in older Excel versions, and for some two-way lookups.

On typical business workbooks the difference is small. Speed is dominated by range size and calculation settings. Large exact-match lookups on unsorted data are slow with any function; sorted data with binary search or a helper key column helps more than switching functions.

The Excel version does not have XLOOKUP, usually Excel 2016 or 2019. Anyone opening the file in those versions sees #NAME?. Use INDEX/MATCH if the workbook must be shared with users on older versions.

With XLOOKUP, multiply conditions: =XLOOKUP(1, (A:A=G2)*(B:B=H2), C:C). With INDEX/MATCH, use =INDEX(C:C, MATCH(1, (A:A=G2)*(B:B=H2), 0)), which in older Excel must be entered as an array formula. A concatenated helper key column is often the clearest option.

Not necessarily. Working VLOOKUPs with exact match are fine. Replace them when columns are inserted often (VLOOKUP's column number breaks), when you need left lookups, or when you are restructuring the workbook anyway.