XLOOKUP: find a match and keep exceptions visible
A price list lookup needs to distinguish three states: a real match, a genuine zero and a product that does not exist in the list. XLOOKUP can express that distinction without burying the problem.
Formula syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
What goes into it
lookup_value
Value to find.
lookup_array
One-dimensional range to search.
return_array
Range that supplies the result.
if_not_found
Optional fallback when no match exists.
match/search mode
Optional controls for match type and search direction.
A quick example first
=XLOOKUP(E2,A:A,C:C,"Not found")
Finds E2 in column A and returns the same-row value from C.
=XLOOKUP(E2,A:A,C:C,"",0,-1)
Uses exact match and searches from the last item upward.
Inventory lookup: first match, last match and missing IDs
Enter this deliberately small inventory log in A1:C5. Type P-101 in E2. The duplicate ID is intentional: it lets you see which record the search returns. Prices here are sample data, not current market prices.
| Row | A: Product ID | B: Item | C: Price |
|---|---|---|---|
| 2 | P-101 | Notebook | 40 |
| 3 | P-102 | Pen | 10 |
| 4 | P-103 | Folder | 25 |
| 5 | P-101 | Notebook — revised | 45 |
| Formula | Expected result / interpretation |
|---|---|
| =XLOOKUP(E2,A2:A5,C2:C5,”Missing ID”) | 40 — first exact match |
| =XLOOKUP(E2,A2:A5,C2:C5,”Missing ID”,0,-1) | 45 — last exact match |
| =XLOOKUP(“P-999″,A2:A5,C2:C5,”Missing ID”) | Missing ID |
| =XLOOKUP(“Note*”,B2:B5,C2:C5,”Missing item”,2) | 40 — first wildcard match |
The default search moves from the first row to the last. Setting search_mode to -1 reverses that direction; it does not sort by date. The last row is the latest record only if your data is arranged that way. Wildcards need match_mode 2: an asterisk matches a run of characters, while a question mark matches one character. Keep literal wildcard searches separate from exact product-ID lookups.
Before you copy this into a real sheet
Use Excel for Microsoft 365, Excel 2021 or Excel 2024 for this example; XLOOKUP is not available in Excel 2016 or 2019. In those older versions, =INDEX(C2:C5,MATCH(E2,A2:A5,0)) returns the first matching price. Test a missing ID before adding error handling. Lookup and return ranges must align: A2:A5 with C3:C6 may return a plausible but wrong row. A2:A5 with C2:C4 has inconsistent dimensions. If an existing ID is not found, compare data types and invisible spaces before changing match mode. Do not use approximate matching to conceal dirty IDs.
Useful habits
- Keep lookup and return arrays the same height or width.
- Use the built-in if_not_found argument instead of wrapping every lookup in IFERROR.
- Explicit match/search modes make intent clearer in complex workbooks.
Where people get caught out
- Using arrays with different shapes.
- Assuming text IDs and numeric IDs match automatically when their types differ.
Other functions worth a look
A detail worth remembering
A blank return cell is not the same as a missing lookup key. Try those cases separately before relying on a fallback in a report.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.