XLOOKUP in Excel: syntax and finance examples, from ledger to customer master

XLOOKUP finds a value in one range and returns the item in the same position from another range. It matches exactly unless you ask otherwise, it can return a column to the left of the key, and it takes a label to show when nothing is found. Against VLOOKUP it needs no column number and no key in the first column; the full comparison, with INDEX MATCH, is in XLOOKUP vs VLOOKUP vs INDEX MATCH.

XLOOKUP is in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Microsoft's XLOOKUP page states that it is not available in Excel 2016 or Excel 2019, where it shows #NAME?.

The syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Microsoft warns that the binary modes return invalid results if the lookup array is not sorted. Modes 1 and -1 need no sorting.

The data: a ledger and a customer master

Both ranges are Excel Tables, named Ledger and Customers. Tables let formulas use structured references such as Customers[ID], which need no $ anchors and grow as rows are added.

Ledger, columns A to C:

Customers:

C-1099 is on an invoice but not in the master. Every example below has to deal with it. If your master is not yet one clean table, start with how to build a customer master from scratch.

Example 1: segment for every invoice

Add a Segment column to Ledger. In D2:

=XLOOKUP([@[Customer ID]],Customers[ID],Customers[Segment],"Not in master")

Because Ledger is a table, the formula fills the whole column by itself.

The fourth argument turns the #N/A for C-1099 into a label you can filter, count and total.

Example 2: several columns in one formula

A return array several columns wide gives several results. On a sheet named Lookup, with invoices in column A and customer IDs in column B, put this in C2 and fill it down to C7:

=XLOOKUP(B2,Customers[ID],Customers[[Name]:[Account manager]])

Customers[[Name]:[Account manager]] is the three adjacent columns Name, Segment and Account manager, so each formula spills across C to E:

Two rules come with spilling, both in Microsoft's page on spilled array behavior. The cells the result spills into must be empty, or the formula shows #SPILL!. And spilled formulas are not supported inside Excel tables, which is why this one sits on a plain sheet rather than in Ledger.

Example 3: the latest price with search_mode -1

Prices holds every price change, oldest first:

With a SKU in A2, in B2:

=XLOOKUP(A2,Prices[SKU],Prices[Price],,0,-1)

The empty fourth argument keeps the default not-found result, 0 asks for an exact match, and -1 searches from the bottom up. SKU-200 returns $53.50, the price from August 3, 2026; the default top-down search would return $48.00, the January price. SKU-310 returns $23.75. This works only while new prices are added at the bottom, so keep the table in date order.

Example 4: discount band with approximate match

Bands:

Add a Discount rate column to Ledger. In E2:

=XLOOKUP([@[Net amount]],Bands[From],Bands[Rate],,-1)

match_mode -1 means exact match or the next smaller item. There is no 12,400 in the band table, so INV-1001 gets the rate for 10,000:

The band table does not have to be sorted for this. Sorting matters only if you add search_mode 2 or -2 for a binary search.

Example 5: two criteria

Region alone is not unique: Harlow Foods and Northgate Retail are both Midwest. To find the Mid-market customer in the Midwest, put Midwest in G2, Mid-market in H2, and in I2:

=XLOOKUP(1,(Customers[Region]=G2)*(Customers[Segment]=H2),Customers[Name],"None")

Each comparison returns TRUE or FALSE for every row, and multiplying them gives 1 only where both are true: {0;0;0;1;0}. XLOOKUP finds the 1 and returns Northgate Retail. Midwest and Enterprise return Harlow Foods; Midwest and Small return None. On a large master, a helper column such as =[@Region]&"|"&[@Segment] does the same job with an ordinary one-column lookup and is easier to audit.

The check: nothing silently dropped

A lookup column is only trusted once its totals tie back to the ledger. On a Check sheet, list every label the Segment column can hold in A2:A5 and in B2, filled down:

=SUMIFS(Ledger[Net amount],Ledger[Segment],A2)

Then, in B7:

=SUM(B2:B5)-SUM(Ledger[Net amount])

It must be 0. Enterprise is 12,400 + 8,760 + 7,200 = 28,360, Mid-market 3,150 + 5,500 = 8,650, and the 1,980 that matched nothing stays visible as its own line instead of disappearing. If a segment label is missing from column A, or a formula returns a blank, the difference is not 0 and you know before anyone reads the report. The same test is the first of the five checks before you trust a spreadsheet total, and it is the core of reconciling the CRM to the ledger.

Where it goes wrong

• IDs stored as text in one table and as numbers in the other. 1001 and "1001" do not match exactly. Fix the type at source, or convert one side with VALUE or TEXT.
• Trailing spaces from a system export. "C-1004 " does not match "C-1004". TRIM the key column once, in the data, not inside every formula.
• An if_not_found of 0 or "". A missing customer then looks like a real customer with nothing in it. Use a visible label and reconcile the totals.
• Duplicate IDs in the master. XLOOKUP returns the first match (or the last, with search_mode -1) and never tells you there was a second. Flag duplicates in the master with =COUNTIF(Customers[ID],[@ID])>1. IDs that change after a system migration are a related trap, covered in identifier drift and re-keyed accounts.
• A colleague on Excel 2019 or earlier. Every XLOOKUP shows #NAME?. If the file travels, VLOOKUP with FALSE or INDEX MATCH will open anywhere.

Joining the ledger to the master without formulas

Covirage joins the ledger to the customer master itself, lists every unmatched ID instead of dropping it, and checks the joined total against the ledger total. Its deterministic tools do the matching and the sums; the external AI model only explains the result. Upload the ledger and the customer master as they are, and see how it works for finance teams. For where lookups fit in a wider workbook, read sales analytics in Excel.

title: "XLOOKUP in Excel: syntax and finance examples, from ledger to customer master" description: "The XLOOKUP syntax with every argument and default, worked on a six-invoice ledger and a five-customer master. Five examples cover the segment for each invoice, several columns at once, the latest price, a discount band and a two-criteria lookup, followed by the control-total check that proves no invoice was dropped." seoTitle: "XLOOKUP in Excel: syntax and finance examples" metaDescription: "XLOOKUP finds a value in one range and returns the matching value from another. Syntax, arguments and ledger-to-customer-master examples you can copy." date: 2026-09-30 category: guides industry: finance-teams solution: excel-analysis keywords: ["xlookup", "xlookup excel", "xlookup formula", "xlookup multiple criteria", "xlookup not found", "xlookup return multiple columns", "xlookup approximate match"] answer: "XLOOKUP searches one range for a value and returns the matching item from another: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). It defaults to an exact match, can look left, returns several columns at once and has a built-in not-found value. It is in Microsoft 365, Excel 2021 and later, and Excel for the web." faq:

• q: "Is XLOOKUP available in Excel 2016 or 2019?" a: "No. XLOOKUP is in Microsoft 365, Excel 2021, Excel 2024, Excel for the web and the current mobile apps. In Excel 2016 and 2019 it returns #NAME?, so use INDEX and MATCH if the workbook will be opened there."
• q: "Why does XLOOKUP return #N/A?" a: "The lookup value was not found in the lookup array, usually because of a trailing space, a number stored as text, or a genuinely missing record. Set the if_not_found argument to a label so missing rows show clearly rather than as errors."
• q: "Can XLOOKUP return multiple columns?" a: "Yes. Give a return array several columns wide and the result spills across adjacent cells. The cells to the right must be empty, or Excel shows #SPILL!. A spilling formula cannot sit inside an Excel table, so put it on the grid next to one."
• q: "Can XLOOKUP use multiple criteria?" a: "Yes. Multiply Boolean conditions to build an array of ones and zeros and look up 1: =XLOOKUP(1,(range1=value1)*(range2=value2),return_range). It is an exact match. On very large tables a helper key column that joins the criteria is easier to audit."

XLOOKUP finds a value in one range and returns the item in the same position from another range. It matches exactly unless you ask otherwise, it can return a column to the left of the key, and it takes a label to show when nothing is found. Against VLOOKUP it needs no column number and no key in the first column; the full comparison, with INDEX MATCH, is in XLOOKUP vs VLOOKUP vs INDEX MATCH.

XLOOKUP is in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Microsoft's XLOOKUP page states that it is not available in Excel 2016 or Excel 2019, where it shows #NAME?.

The syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Microsoft warns that the binary modes return invalid results if the lookup array is not sorted. Modes 1 and -1 need no sorting.

The data: a ledger and a customer master

Both ranges are Excel Tables, named Ledger and Customers. Tables let formulas use structured references such as Customers[ID], which need no $ anchors and grow as rows are added.

Ledger, columns A to C:

Customers:

C-1099 is on an invoice but not in the master. Every example below has to deal with it. If your master is not yet one clean table, start with how to build a customer master from scratch.

Example 1: segment for every invoice

Add a Segment column to Ledger. In D2:

=XLOOKUP([@[Customer ID]],Customers[ID],Customers[Segment],"Not in master")

Because Ledger is a table, the formula fills the whole column by itself.

The fourth argument turns the #N/A for C-1099 into a label you can filter, count and total.

Example 2: several columns in one formula

A return array several columns wide gives several results. On a sheet named Lookup, with invoices in column A and customer IDs in column B, put this in C2 and fill it down to C7:

=XLOOKUP(B2,Customers[ID],Customers[[Name]:[Account manager]])

Customers[[Name]:[Account manager]] is the three adjacent columns Name, Segment and Account manager, so each formula spills across C to E:

Two rules come with spilling, both in Microsoft's page on spilled array behavior. The cells the result spills into must be empty, or the formula shows #SPILL!. And spilled formulas are not supported inside Excel tables, which is why this one sits on a plain sheet rather than in Ledger.

Example 3: the latest price with search_mode -1

Prices holds every price change, oldest first:

With a SKU in A2, in B2:

=XLOOKUP(A2,Prices[SKU],Prices[Price],,0,-1)

The empty fourth argument keeps the default not-found result, 0 asks for an exact match, and -1 searches from the bottom up. SKU-200 returns $53.50, the price from August 3, 2026; the default top-down search would return $48.00, the January price. SKU-310 returns $23.75. This works only while new prices are added at the bottom, so keep the table in date order.

Example 4: discount band with approximate match

Bands:

Add a Discount rate column to Ledger. In E2:

=XLOOKUP([@[Net amount]],Bands[From],Bands[Rate],,-1)

match_mode -1 means exact match or the next smaller item. There is no 12,400 in the band table, so INV-1001 gets the rate for 10,000:

The band table does not have to be sorted for this. Sorting matters only if you add search_mode 2 or -2 for a binary search.

Example 5: two criteria

Region alone is not unique: Harlow Foods and Northgate Retail are both Midwest. To find the Mid-market customer in the Midwest, put Midwest in G2, Mid-market in H2, and in I2:

=XLOOKUP(1,(Customers[Region]=G2)*(Customers[Segment]=H2),Customers[Name],"None")

Each comparison returns TRUE or FALSE for every row, and multiplying them gives 1 only where both are true: {0;0;0;1;0}. XLOOKUP finds the 1 and returns Northgate Retail. Midwest and Enterprise return Harlow Foods; Midwest and Small return None. On a large master, a helper column such as =[@Region]&"|"&[@Segment] does the same job with an ordinary one-column lookup and is easier to audit.

The check: nothing silently dropped

A lookup column is only trusted once its totals tie back to the ledger. On a Check sheet, list every label the Segment column can hold in A2:A5 and in B2, filled down:

=SUMIFS(Ledger[Net amount],Ledger[Segment],A2)

Then, in B7:

=SUM(B2:B5)-SUM(Ledger[Net amount])

It must be 0. Enterprise is 12,400 + 8,760 + 7,200 = 28,360, Mid-market 3,150 + 5,500 = 8,650, and the 1,980 that matched nothing stays visible as its own line instead of disappearing. If a segment label is missing from column A, or a formula returns a blank, the difference is not 0 and you know before anyone reads the report. The same test is the first of the five checks before you trust a spreadsheet total, and it is the core of reconciling the CRM to the ledger.

Where it goes wrong

• IDs stored as text in one table and as numbers in the other. 1001 and "1001" do not match exactly. Fix the type at source, or convert one side with VALUE or TEXT.
• Trailing spaces from a system export. "C-1004 " does not match "C-1004". TRIM the key column once, in the data, not inside every formula.
• An if_not_found of 0 or "". A missing customer then looks like a real customer with nothing in it. Use a visible label and reconcile the totals.
• Duplicate IDs in the master. XLOOKUP returns the first match (or the last, with search_mode -1) and never tells you there was a second. Flag duplicates in the master with =COUNTIF(Customers[ID],[@ID])>1. IDs that change after a system migration are a related trap, covered in identifier drift and re-keyed accounts.
• A colleague on Excel 2019 or earlier. Every XLOOKUP shows #NAME?. If the file travels, VLOOKUP with FALSE or INDEX MATCH will open anywhere.

Joining the ledger to the master without formulas

Covirage joins the ledger to the customer master itself, lists every unmatched ID instead of dropping it, and checks the joined total against the ledger total. Its deterministic tools do the matching and the sums; the external AI model only explains the result. Upload the ledger and the customer master as they are, and see how it works for finance teams. For where lookups fit in a wider workbook, read sales analytics in Excel. 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.