Office Dễ Hiểu

How to use XLOOKUP in Excel

Look up a product code and return its price, with a working example and a missing-value message.

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

ExcelF2XLOOKUP

Before

E2 contains SP02. The source table has three product codes.

After

F2 returns 5,000 from column C.

Check the result

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")
CodeProductUnit price
SP01A4 paper65,000
SP02Ballpoint pen5,000
SP03Folder12,000
Result5,000
Product code (A)Product (B)Unit price (C)
SP01A4 paper65000
SP02Ballpoint pen5000
SP03Folder12000

If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.

Step-by-step instructions

  1. Enter the sample data in A1:C4, keeping product codes in column A and prices in column C.
  2. Type SP02 in E2. Select F2 and enter the formula shown below.
    =XLOOKUP(E2,A2:A4,C2:C4,"Not found")
  3. Press Enter. F2 should return 5000, the price for SP02.
  4. 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