Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])| Argument | What it means |
|---|---|
lookup_value | What to find. |
lookup_array | The column or row to search. |
return_array | The column or row to return a value from. |
if_not_found | Optional. What to show when there is no match. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Price list (fictional)
| Row | A | B | C |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-101 | Desk lamp | 24.9 |
| 3 | P-102 | Notebook | 3.5 |
| 4 | P-103 | Mouse | 15 |
| 5 | P-104 | Keyboard | 32 |
| 6 | P-105 | Monitor stand | 21 |
=XLOOKUP("P-104",A2:A6,C2:C6)Price for P-104
=XLOOKUP("Mouse",B2:B6,A2:A6)Look up a code by product name (looking left)
=XLOOKUP("P-999",A2:A6,C2:C6,"Not listed")A friendly result when nothing matches
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use XLOOKUP at work
- Operations
Find the unit cost for a SKU.
Data: Stock levels (fictional)=XLOOKUP("A-120",A2:A6,E2:E6) - HR
Find a person’s department.
Data: Team list (fictional)=XLOOKUP("Dina Ali",A2:A8,B2:B8)
Common mistakes and tips
- XLOOKUP is not in Excel 2019 or older. Use INDEX with MATCH there.
- The two ranges must be the same size.
Works in
Microsoft 365, Excel 2021 or later, and Google Sheets. Not in Excel 2019 or older.
Lessons that use it
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.