Excel formulas

XLOOKUP vs. INDEX/MATCH: which should you use?

8 min read

A side-by-side look at both approaches on a real lookup, where each one breaks, and a simple rule for choosing.

The task

You have a list of customer accounts and a cell holding an account ID. You want the balance for that account. This is the most common job in spreadsheet work, and Excel gives you several ways to do it. Two of them deserve your attention: XLOOKUP, the newer function, and INDEX/MATCH, the pair that has been the professional's choice for years.

The short answer

Use XLOOKUP when everyone opening the workbook has a compatible Excel version. Use INDEX/MATCH when the workbook needs to work in older versions or follow an established team standard.

Here is the sample data used in this guide, with the ID to look up in cell E2.

Account IDCustomerBalanceRegion

Imagine this in columns A to D, with headings in row 1 and the eight accounts in rows 2 to 9.

The same lookup, two ways

=XLOOKUP(E2,A2:A9,C2:C9,"Not found") =IFNA(INDEX(C2:C9,MATCH(E2,A2:A9,0)),"Not found")

Both return the balance for the ID in E2, or "Not found" if the ID is not in the list. The numbered labels mark the same four jobs in each formula, so you can match them up by number as well as by color.

XLOOKUP
E21. What to find
A2:A92. Where to look
C2:C93. What to return
"Not found"4. What shows if nothing matches
INDEX / MATCH
IFNA(4. Catches the error when nothing matches
INDEX(C2:C93. What to return
MATCH(E21. What to find
A2:A92. Where to look
0Extra: exact match only
"Not found"4. IFNA's fallback text

XLOOKUP does it in one function. INDEX/MATCH splits it in two: MATCH finds the row position, and INDEX returns the value from that position. When MATCH finds nothing it produces an #N/A error, so the pair is wrapped in IFNA to show "Not found" instead. The 0 inside MATCH is not one of the four jobs. It is an extra setting that tells MATCH to look for an exact match.

How both handle inserted columns

This is a shared strength, not a difference. Both methods reference the return range directly (C2:C9 here), so when you insert a column in the worksheet, Excel can adjust those references for you. The cell that holds the lookup ID moves too: insert a column before Balance and E2 becomes F2. Try it.

Insert a column

Pick an account, then insert a new column before Balance. Both formulas keep returning the right answer.

Yellow marks the inserted column. Teal marks Balance, which moves from column C to column D. Changed references in the formulas are bold with a dotted underline.

MethodFormula nowResultCorrect?

Where XLOOKUP saves more work

XLOOKUP can return a whole row of results from one formula. Here it looks up the account and returns Customer, Balance and Region together, using the same eight accounts.

=XLOOKUP(E2,A2:A9,B2:D9,"Not found")

This returns Customer, Balance, and Region together. Put the formula in an empty area outside the source data, with room for the results to spread across three columns.

One formula, three results

Pick an account. The formula sits in G2, and Excel fills G2, H2 and I2 by itself.

Customer (G2)
Balance (H2)
Region (I2)

With INDEX/MATCH you would usually write one formula per column.

Watch for #SPILL! If any cell the results need to fill is not empty, Excel shows a #SPILL! error instead of the results. Clear the blocking cells and the formula recovers.

Finding the last match

By default both methods stop at the first match. XLOOKUP can search from the bottom instead, with two extra settings.

=XLOOKUP(E2,A2:A9,C2:C9,"Not found",0,-1)

The 0 keeps the match exact, and the final -1 searches from the bottom of the list upward, so the last matching row wins. That row is the latest record only when the data is ordered that way. Searching backward does not find the newest date by itself. It finds whichever matching row sits lowest.

Top or bottom

This list is ordered from oldest to newest, so the bottom match is the latest one.

RowAccount IDPostedBalanceStatus

How they compare

XLOOKUPINDEX/MATCH
Works inMicrosoft 365, Excel 2021, Excel 2024, and Excel for the web.Works in older Excel versions, including Excel 2016 and 2019.
Exact matchXLOOKUP uses exact matching by default.MATCH needs 0 for exact matching.
If nothing is foundBuilt inWrap it in IFNA
Look to the leftYesYes
Inserted columnsReferences adjustReferences adjust
Return several columnsYes, in one formulaNeeds more work
Find the last matchYes, with a search optionNeeds a workaround
ReadabilityOne function, easy to scanTwo functions, more to learn

Where each one breaks

  • XLOOKUP in an older Excel. Anyone opening the file in Excel 2019 or earlier sees a #NAME? error. This is the main reason people still use INDEX/MATCH.
  • INDEX/MATCH without the 0. Leave it off and MATCH does an approximate match, which can quietly return the wrong row on unsorted data. Always type the 0 for exact matches.
  • INDEX/MATCH with mismatched ranges. MATCH returns a position within its lookup range. INDEX uses that position within its return range. If the ranges start on different rows, you can retrieve the wrong record or reach an invalid position. =IFNA(INDEX(C2:C9,MATCH(E2,A1:A9,0)),"Not found")

    Here the lookup range starts one row early, at the header. For A-1004, MATCH returns position 5, but the fifth item in C2:C9 belongs to A-1005 (Echo Partners, $2,150) instead of A-1004 ($7,300). For A-1008, MATCH returns position 9, which is past the end of the eight-row return range, so INDEX gives a #REF! error. Start both ranges on the same row and keep them the same size.

  • Both: data that looks the same but is not. If a match fails or returns the wrong record, check three things:
    • Are both IDs stored as the same type, text or number?
    • Does either value contain extra spaces?
    • Do the lookup and return ranges cover the same records?
  • Both: duplicates. Both return the first match by default. If duplicates are valid, decide which record you want before choosing the formula.

A simple rule for choosing

  • Use XLOOKUP when everyone who opens the file has Microsoft 365, Excel 2021, Excel 2024, or Excel for the web. It is shorter, exact by default, easier to read, and it can return several columns from one formula.
  • Use INDEX/MATCH when the file has to work in older versions of Excel, such as 2016 or 2019, or when you are following a team convention.

Whichever you choose, use bounded ranges that start on the same row, and decide how you want duplicates handled before you build.

Need lookups your team can maintain?

We build spreadsheets with clear formulas, consistent inputs, and checks that catch missing matches. Start with a free 30 minute strategy session.

Book a free strategy session