XLOOKUP function
XLOOKUP searches one range and returns the value at the same position in another. The default is an exact match.
Lookup and reference
Where can you use it?
Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.
Scenario in the workbook
Look up a region by employee ID. Supply a message for missing codes.
A worked example from the workbook
| Row | A | B | C |
|---|---|---|---|
| 6 | Value | Comparison | Region |
| 7 | E01 | E01 | North |
| 8 | E02 | E02 | East |
| 9 | E99 | E03 | West |
=XLOOKUP(A7,$B$7:$B$9,$C$7:$C$9,"Missing")Expected resultNorth
See the inputs and instructions for all 3 examplesCopyable formulas
=XLOOKUP(E2,B2:B4,A2:A4,"Not found")=ÇAPRAZARA(E2;B2:B4;A2:A4;"Bulunamadı")Sample data
Enter the headers and sample records in A1:B4.
| Price | Product ID |
|---|---|
| 125 | K-10 |
| 150 | K-20 |
| 95 | K-30 |
- E2 = K-20
Enter the formula in E3.
To try the Turkish formula, use the sample data on the Turkish page.
Expected result
150
How it works
The return range can be to the left of the lookup range. K-99 uses the fourth argument. With duplicate IDs, this formula returns the first match from the top.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Compatibility
Excel for Microsoft 365, Excel 2021, and Excel 2024.
Related pages
Worked examples
Look up a region by employee ID. Supply a message for missing codes.
Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.
| Row | A | B | C |
|---|---|---|---|
| 6 | Value | Comparison | Region |
| 7 | E01 | E01 | North |
| 8 | E02 | E02 | East |
| 9 | E99 | E03 | West |
| Case | Formula | Expected result |
|---|---|---|
| Record 1 | =XLOOKUP(A7,$B$7:$B$9,$C$7:$C$9,"Missing") |
North |
| Record 2 | =XLOOKUP(A8,$B$7:$B$9,$C$7:$C$9,"Missing") |
East |
| Record 3 | =XLOOKUP(A9,$B$7:$B$9,$C$7:$C$9,"Missing") |
Missing |