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 ID | Customer | Balance | Region |
|---|
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 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.
| Method | Formula now | Result | Correct? |
|---|
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.
With INDEX/MATCH you would usually write one formula per column.
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.
| Row | Account ID | Posted | Balance | Status |
|---|
How they compare
| XLOOKUP | INDEX/MATCH | |
|---|---|---|
| Works in | Microsoft 365, Excel 2021, Excel 2024, and Excel for the web. | Works in older Excel versions, including Excel 2016 and 2019. |
| Exact match | XLOOKUP uses exact matching by default. | MATCH needs 0 for exact matching. |
| If nothing is found | Built in | Wrap it in IFNA |
| Look to the left | Yes | Yes |
| Inserted columns | References adjust | References adjust |
| Return several columns | Yes, in one formula | Needs more work |
| Find the last match | Yes, with a search option | Needs a workaround |
| Readability | One function, easy to scan | Two 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.