XLOOKUP vs VLOOKUP vs INDEX MATCH: which to use, on the same ledger

Use XLOOKUP when everyone who opens the file has Microsoft 365 or Excel 2021 or later. Use INDEX MATCH when the file must also open in Excel 2016 or 2019: it does the same jobs, with more typing. Keep VLOOKUP for maintaining old files, because it matches approximately by default, only looks right and depends on a column number.

The short answer

Two cells in that table catch people out. MATCH, like VLOOKUP, is approximate unless you type 0: Microsoft's MATCH page gives 1 as the default. And Microsoft's XLOOKUP page states that XLOOKUP is not available in Excel 2016 and Excel 2019.

The same lookup three ways

The task: the account manager for each invoice. The Ledger sheet has invoices in A and customer IDs in B. The Customers sheet is the customer master:

VLOOKUP in Ledger!C2:

=VLOOKUP(B2,Customers!$A$2:$D$6,4,FALSE)

INDEX MATCH in Ledger!D2:

=INDEX(Customers!$D$2:$D$6,MATCH(B2,Customers!$A$2:$A$6,0))

XLOOKUP in Ledger!E2:

=XLOOKUP(B2,Customers!$A$2:$A$6,Customers!$D$2:$D$6,"Not in master")

Filled down to row 7:

On the five matched rows the three agree. VLOOKUP names the whole table and counts to column 4. INDEX MATCH names the column to return and the column to search separately. XLOOKUP does the same in one function.

Where they differ: a missing ID

C-1099 is on an invoice but not in the master. VLOOKUP and INDEX MATCH return #N/A, which breaks any SUM over the column until someone wraps the formula in IFNA. XLOOKUP returns the label in its fourth argument, "Not in master", which can be counted and totaled as its own line. None of the three says why the ID is missing; that needs a reconciliation, not a lookup.

Where they differ: an inserted column

Insert a Region column between B and C in the master. Segment moves to D and Account manager to E, and Excel rewrites every reference:

VLOOKUP's range widens to $A$2:$E$6, but the 4 is typed in, and column 4 is now Segment. Every row returns a segment where an account manager should be, with no error. INDEX MATCH and XLOOKUP point at the column itself, so the reference moves with it. The other ways VLOOKUP fails on a ledger are in VLOOKUP in Excel.

Looking left

The ID for "Kestrel Supplies" sits to the left of the name. INDEX MATCH:

=INDEX(Customers!$A$2:$A$6,MATCH("Kestrel Supplies",Customers!$B$2:$B$6,0))

XLOOKUP:

=XLOOKUP("Kestrel Supplies",Customers!$B$2:$B$6,Customers!$A$2:$A$6)

Both return C-1003. VLOOKUP cannot: Microsoft's VLOOKUP page requires the lookup value to be in the first column of the table, and the only fix is to rebuild the table with the name first.

Two-way lookups

A Budget sheet with cost centers down column A and months across row 1 (May to December continue in F to M):

With Finance in G1 and Mar in H1 on the report sheet, INDEX takes a row number from one MATCH and a column number from another:

=INDEX(Budget!$B$2:$M$4,MATCH($G$1,Budget!$A$2:$A$4,0),MATCH($H$1,Budget!$B$1:$M$1,0))

The MATCHes return 2 and 3, and INDEX returns $41,500. The XLOOKUP version nests one inside the other: the inner XLOOKUP returns the whole March column, and the outer one picks Finance from it:

=XLOOKUP($G$1,Budget!$A$2:$A$4,XLOOKUP($H$1,Budget!$B$1:$M$1,Budget!$B$2:$M$4))

Also $41,500. The same grid shape appears in a cross-sell matrix.

Speed and very large files

Microsoft's Excel performance guidance says three things that matter here:

• Exact-match time is proportional to the number of cells scanned before a match. Keep ranges tight, using tables and structured references rather than whole columns.
• Approximate match on sorted data is fast and barely slows as the range grows, because it works like a binary search. XLOOKUP's binary search modes, 2 and -2, rely on the same sorting.
• VLOOKUP is about 5 percent faster than INDEX MATCH on a single lookup, but one exact MATCH stored in a cell and reused by several INDEX formulas can save significantly more time when you return several columns.

Since Office 365 version 1809, exact-match VLOOKUP, HLOOKUP and MATCH also cache an index when several lookups read the same range. The guidance does not rank XLOOKUP against the other two, and forum speed tests rarely match a real workbook. On lookups with two or more criteria, a helper key column such as =A2&"|"&B2 lets Excel search one column instead of building arrays.

Which to standardize on

For a finance team:

1. XLOOKUP in files that stay inside the team, where everyone has Microsoft 365 or Excel 2021 or later.
2. INDEX MATCH, with 0 in MATCH, in shared models that a bank, auditor or subsidiary may open in Excel 2016 or 2019.
3. VLOOKUP only when maintaining an old file, and always with FALSE.

Whichever you choose, tie the looked-up column back to the source total, as in the check in XLOOKUP in Excel. A lookup that returns the wrong customer is still a lookup that worked.

Where it goes wrong

• XLOOKUP in a model someone opens in Excel 2016 or 2019. Every cell shows #NAME?.
• MATCH with its third argument left out. Like VLOOKUP, it defaults to approximate match and returns a neighbor's row.
• INDEX and MATCH ranges of different sizes or starting rows. INDEX($D$3:$D$7,MATCH(B2,$A$2:$A$6,0)) returns the value one row down.
• Dirty keys. Trailing spaces and numbers stored as text defeat all three equally; none of them cleans a key.
• Speed claims from forum tests. Recalculation cost depends on range sizes and volatile functions more than on which lookup you picked.

Skipping the lookup chain

Covirage joins files by exact keys with its own deterministic tools and shows the unmatched rows and the reconciled total, so no lookup formula has to be chosen or maintained. The external AI model explains the result; it does not do the matching. Upload the files and get joined, reconciled tables; see how it works for finance teams. The same joins sit under sales analytics in Excel and customer concentration in Excel, and when the chain of lookups is what holds the workbook together, read four signs a spreadsheet is no longer enough.

title: "XLOOKUP vs VLOOKUP vs INDEX MATCH: which to use, on the same ledger" description: "XLOOKUP, VLOOKUP and INDEX MATCH compared side by side on one ledger and customer master: default match, missing IDs, inserted columns, left lookups, two-way lookups, speed and version availability. It ends with a rule a finance team can standardize on." seoTitle: "XLOOKUP vs VLOOKUP vs INDEX MATCH compared" metaDescription: "XLOOKUP defaults to exact match, looks left and survives inserted columns; VLOOKUP does none of these. INDEX MATCH does all three, in every Excel." date: 2026-09-30 category: guides industry: finance-teams solution: excel-analysis keywords: ["xlookup vs vlookup", "index match", "index match vs vlookup", "xlookup vs index match", "is xlookup better than vlookup", "index match formula", "vlookup vs xlookup speed"] answer: "Use XLOOKUP if everyone opening the file has Microsoft 365 or Excel 2021 or later: it defaults to exact match, can look left, survives inserted columns and has a not-found argument. Use INDEX MATCH when the file must work in older Excel: it does the same jobs in every version. VLOOKUP is the weakest: approximate by default, right-only, and tied to a column number." faq:

• q: "Is XLOOKUP better than VLOOKUP?" a: "For most jobs, yes: exact match by default, left lookups, a not-found argument, and no column number to break. Its one drawback is availability: Excel 2019 and earlier do not have it, so shared files may show #NAME?."
• q: "Is INDEX MATCH still worth learning?" a: "Yes. It works in every Excel version and in Google Sheets, handles left and two-way lookups, and appears in most existing finance models. Anyone maintaining older workbooks will meet it."
• q: "Is XLOOKUP faster than INDEX MATCH?" a: "Differences are small for typical finance workbooks. Microsoft's guidance is that exact-match time grows with the cells scanned, and that approximate match on sorted keys is fast, like a binary search; XLOOKUP's binary search modes need the same sorting. Range size matters more than the function chosen."
• q: "What does the 0 in MATCH mean?" a: "The third argument of MATCH is match_type. 0 means exact match. 1, the default, finds the largest value less than or equal to the lookup value in ascending data, and -1 the smallest value greater than or equal in descending data."

Use XLOOKUP when everyone who opens the file has Microsoft 365 or Excel 2021 or later. Use INDEX MATCH when the file must also open in Excel 2016 or 2019: it does the same jobs, with more typing. Keep VLOOKUP for maintaining old files, because it matches approximately by default, only looks right and depends on a column number.

The short answer

Two cells in that table catch people out. MATCH, like VLOOKUP, is approximate unless you type 0: Microsoft's MATCH page gives 1 as the default. And Microsoft's XLOOKUP page states that XLOOKUP is not available in Excel 2016 and Excel 2019.

The same lookup three ways

The task: the account manager for each invoice. The Ledger sheet has invoices in A and customer IDs in B. The Customers sheet is the customer master:

VLOOKUP in Ledger!C2:

=VLOOKUP(B2,Customers!$A$2:$D$6,4,FALSE)

INDEX MATCH in Ledger!D2:

=INDEX(Customers!$D$2:$D$6,MATCH(B2,Customers!$A$2:$A$6,0))

XLOOKUP in Ledger!E2:

=XLOOKUP(B2,Customers!$A$2:$A$6,Customers!$D$2:$D$6,"Not in master")

Filled down to row 7:

On the five matched rows the three agree. VLOOKUP names the whole table and counts to column 4. INDEX MATCH names the column to return and the column to search separately. XLOOKUP does the same in one function.

Where they differ: a missing ID

C-1099 is on an invoice but not in the master. VLOOKUP and INDEX MATCH return #N/A, which breaks any SUM over the column until someone wraps the formula in IFNA. XLOOKUP returns the label in its fourth argument, "Not in master", which can be counted and totaled as its own line. None of the three says why the ID is missing; that needs a reconciliation, not a lookup.

Where they differ: an inserted column

Insert a Region column between B and C in the master. Segment moves to D and Account manager to E, and Excel rewrites every reference:

VLOOKUP's range widens to $A$2:$E$6, but the 4 is typed in, and column 4 is now Segment. Every row returns a segment where an account manager should be, with no error. INDEX MATCH and XLOOKUP point at the column itself, so the reference moves with it. The other ways VLOOKUP fails on a ledger are in VLOOKUP in Excel.

Looking left

The ID for "Kestrel Supplies" sits to the left of the name. INDEX MATCH:

=INDEX(Customers!$A$2:$A$6,MATCH("Kestrel Supplies",Customers!$B$2:$B$6,0))

XLOOKUP:

=XLOOKUP("Kestrel Supplies",Customers!$B$2:$B$6,Customers!$A$2:$A$6)

Both return C-1003. VLOOKUP cannot: Microsoft's VLOOKUP page requires the lookup value to be in the first column of the table, and the only fix is to rebuild the table with the name first.

Two-way lookups

A Budget sheet with cost centers down column A and months across row 1 (May to December continue in F to M):

With Finance in G1 and Mar in H1 on the report sheet, INDEX takes a row number from one MATCH and a column number from another:

=INDEX(Budget!$B$2:$M$4,MATCH($G$1,Budget!$A$2:$A$4,0),MATCH($H$1,Budget!$B$1:$M$1,0))

The MATCHes return 2 and 3, and INDEX returns $41,500. The XLOOKUP version nests one inside the other: the inner XLOOKUP returns the whole March column, and the outer one picks Finance from it:

=XLOOKUP($G$1,Budget!$A$2:$A$4,XLOOKUP($H$1,Budget!$B$1:$M$1,Budget!$B$2:$M$4))

Also $41,500. The same grid shape appears in a cross-sell matrix.

Speed and very large files

Microsoft's Excel performance guidance says three things that matter here:

• Exact-match time is proportional to the number of cells scanned before a match. Keep ranges tight, using tables and structured references rather than whole columns.
• Approximate match on sorted data is fast and barely slows as the range grows, because it works like a binary search. XLOOKUP's binary search modes, 2 and -2, rely on the same sorting.
• VLOOKUP is about 5 percent faster than INDEX MATCH on a single lookup, but one exact MATCH stored in a cell and reused by several INDEX formulas can save significantly more time when you return several columns.

Since Office 365 version 1809, exact-match VLOOKUP, HLOOKUP and MATCH also cache an index when several lookups read the same range. The guidance does not rank XLOOKUP against the other two, and forum speed tests rarely match a real workbook. On lookups with two or more criteria, a helper key column such as =A2&"|"&B2 lets Excel search one column instead of building arrays.

Which to standardize on

For a finance team:

1. XLOOKUP in files that stay inside the team, where everyone has Microsoft 365 or Excel 2021 or later.
2. INDEX MATCH, with 0 in MATCH, in shared models that a bank, auditor or subsidiary may open in Excel 2016 or 2019.
3. VLOOKUP only when maintaining an old file, and always with FALSE.

Whichever you choose, tie the looked-up column back to the source total, as in the check in XLOOKUP in Excel. A lookup that returns the wrong customer is still a lookup that worked.

Where it goes wrong

• XLOOKUP in a model someone opens in Excel 2016 or 2019. Every cell shows #NAME?.
• MATCH with its third argument left out. Like VLOOKUP, it defaults to approximate match and returns a neighbor's row.
• INDEX and MATCH ranges of different sizes or starting rows. INDEX($D$3:$D$7,MATCH(B2,$A$2:$A$6,0)) returns the value one row down.
• Dirty keys. Trailing spaces and numbers stored as text defeat all three equally; none of them cleans a key.
• Speed claims from forum tests. Recalculation cost depends on range sizes and volatile functions more than on which lookup you picked.

Skipping the lookup chain

Covirage joins files by exact keys with its own deterministic tools and shows the unmatched rows and the reconciled total, so no lookup formula has to be chosen or maintained. The external AI model explains the result; it does not do the matching. Upload the files and get joined, reconciled tables; see how it works for finance teams. The same joins sit under sales analytics in Excel and customer concentration in Excel, and when the chain of lookups is what holds the workbook together, read four signs a spreadsheet is no longer enough. To total by a key instead of returning one value, see SUMIFS in Excel, and to merge whole files before they reach a formula, see Power Query in Excel.