XLOOKUP searches one range and returns the matching value from another. Enter the product code in E2, then use the formula below in F2.
Visual walkthrough
Before
E2 contains SP02. The source table has three product codes.
After
F2 returns 5,000 from column C.
Try SP03 for 12,000 and SP99 for Not found.
Try it here
Change the selection to see the result and matching data rows.
=XLOOKUP(E2,A2:A4,C2:C4,"Not found")| Code | Product | Unit price |
|---|---|---|
| SP01 | A4 paper | 65,000 |
| SP02 | Ballpoint pen | 5,000 |
| SP03 | Folder | 12,000 |
Sample data
Download sample CSV| Product code (A) | Product (B) | Unit price (C) |
|---|---|---|
| SP01 | A4 paper | 65000 |
| SP02 | Ballpoint pen | 5000 |
| SP03 | Folder | 12000 |
If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.
Step-by-step instructions
- Enter the sample data in A1:C4, keeping product codes in column A and prices in column C.
- Type SP02 in E2. Select F2 and enter the formula shown below.
=XLOOKUP(E2,A2:A4,C2:C4,"Not found") - Press Enter. F2 should return 5000, the price for SP02.
- Try SP03 for 12000, then SP99 to check the Not found message.
Frequently asked questions
Can XLOOKUP return a value to the left?
Yes. The lookup range and return range are separate, so the return column can be on either side. Keep both ranges the same size.
Reference: Microsoft Support