XLOOKUP Function in Excel
Quick answer
XLOOKUP finds a value in one column and returns the matching value from another, like typing in a supplier invoice number and pulling back its total. It replaces VLOOKUP and HLOOKUP, can look left or right, uses an exact match by default, and lets you set your own “not found” message.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Works in: Excel 365, Excel 2024, Excel 2021, Excel for the web and Google Sheets. Not available in Excel 2019 or Excel 2016.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Required? | What it does |
|---|---|---|
lookup_value | Yes | The value you’re searching for, such as an invoice number or Staff ID. |
lookup_array | Yes | The single column (or row) to search in. |
return_array | Yes | The column (or columns) to bring back a result from. Must be the same height as lookup_array. |
if_not_found | No | What to show if there’s no match. Default: the #N/A error. |
match_mode | No | 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match (*, ?, ~), 3 regex match (Excel 365 only). |
search_mode | No | 1 search first to last (default), -1 search last to first, 2 binary search on data sorted ascending, -2 binary search on data sorted descending. |
How XLOOKUP works
Picture two columns sitting side by side. XLOOKUP runs down the first one (lookup_array) until it finds your value, notes which row it’s on, then reads across to that same row in the second one (return_array). That’s the whole trick.
Because you point at the return column directly instead of counting columns like VLOOKUP makes you do, inserting a new column won’t break your formula. And the return column can sit to the left of the lookup column, which VLOOKUP can’t handle at all.
The last three arguments are optional. Most of the time you’ll only use the first three, plus if_not_found when you want a friendly message instead of an error.
Examples
These use the Bluegum Café workbook. For the invoice examples I’ve added three more July bills to the Invoices sheet, so the data runs from A1 to G6:
| Invoice No | Date | Supplier | Category | Amount ex GST | GST | Total |
|---|---|---|---|---|---|---|
| INV-1001 | 03/07/2025 | Ridgeline Roasters | Coffee beans | $480.00 | $48.00 | $528.00 |
| INV-1002 | 04/07/2025 | Coastal Dairy Co | Milk | $215.50 | $21.55 | $237.05 |
| INV-1003 | 10/07/2025 | Ridgeline Roasters | Coffee beans | $480.00 | $48.00 | $528.00 |
| INV-1004 | 11/07/2025 | Coastal Dairy Co | Milk | $198.20 | $19.82 | $218.02 |
| INV-1005 | 17/07/2025 | Ridgeline Roasters | Coffee beans | $520.00 | $52.00 | $572.00 |
Basic: find a staff member’s hourly rate
On the Staff sheet, type a Staff ID into G2 and put this formula in H2 to pull back that person’s hourly rate:
=XLOOKUP(G2, A2:A3, D2:D3)With S02 in G2, the result is $30.10, Jack Thompson’s rate. As your roster grows, stretch both ranges down to match.
Real-world: pull the GST breakdown for a supplier invoice
When I’m checking bills before a BAS, I want the ex-GST amount, the GST and the total for an invoice in one go. Type the invoice number in I2 and put this in J2:
=XLOOKUP(I2, A2:A6, E2:G6, "Invoice not found")With INV-1002 in I2, the result spills across three cells: $215.50 in J2, $21.55 in K2 and $237.05 in L2. Because return_array is three columns wide, you get all three back from one formula. Type an invoice number that isn’t there and you’ll see “Invoice not found” instead of #N/A.
Advanced: find a supplier’s most recent invoice
A normal lookup returns the first match. To get the latest Ridgeline Roasters invoice, search from the bottom up with search_mode set to -1. Type the supplier in I5 and put this in J5:
=XLOOKUP(I5, C2:C6, A2:A6, "No invoices", 0, -1)The result is INV-1005 (dated 17/07/2025). Leave off the -1 and you’d get INV-1001 instead. This relies on the sheet being in date order, oldest at the top, which is how most accounting exports land.
Guru Tip
If XLOOKUP gives you #N/A and you can see the match sitting right there, it’s almost always a trailing space or a number stored as text. Supplier and payroll exports are notorious for both. I check with =LEN(A2) to spot hidden spaces, and I never slap an if_not_found message on a formula until I’ve fixed the data, because it hides these problems instead of solving them.
Common errors
#N/A: no match found
Cause: the value isn’t in lookup_array, or it only looks the same (extra spaces, text versus number). Fix: clean the data with TRIM or Text to Columns, then add an if_not_found message for values that genuinely aren’t there.
#VALUE!: the ranges don’t line up
Cause: lookup_array and return_array are different heights, such as A2:A6 with G2:G20. Fix: make both ranges start and end on the same rows, or convert the data to an Excel Table so the ranges grow together.
#SPILL!: something’s in the way
Cause: you’re returning more than one column and the cells to the right aren’t empty. Fix: clear those cells, or move the formula somewhere with room to spill.
#NAME?: Excel doesn’t recognise XLOOKUP
Cause: a typo in the function name, or you’re on Excel 2019 or earlier, which doesn’t have XLOOKUP. Fix: check the spelling, or use INDEX and MATCH if you’re stuck on an older version.
#REF!: the other workbook is closed
Cause: the formula looks up data in another workbook that isn’t open. Fix: open the source workbook, or bring the data into the same file.
Grab the template: XLOOKUP
Get the Bluegum Café workbook with every XLOOKUP example from this page already built, so you can see how it works and swap in your own invoices or roster.