Find a price by its code with XLOOKUP

Sample data. Price list in A:B, order in D:E, the formula in F2 is copied down.

The formula

Click the first Price cell of your order (F2 here) and type:

=XLOOKUP(D2,A:A,B:B)

F2 shows 10.00, the price of code B300. Copy it down and every row finds its own price.

• D2 is the code you want to find.
• A:A is the column to search, the codes in your price list.
• B:B is the column to bring back, the prices.

XLOOKUP looks for an exact match by default, so there is no TRUE or FALSE to remember.

Show your own words instead of #N/A

When a code is not in the list, XLOOKUP shows #N/A. Add a fourth part to show a message instead:

=XLOOKUP(D2,A:A,B:B,"Not found")

A code like Z999 that is not in the price list now shows Not found. Text inside a formula goes in straight double quotes.

Price times quantity in one step

Multiply the looked-up price by the quantity in the same cell:

=E2*XLOOKUP(D2,A:A,B:B)

Row 2 orders 2 of B300 at 10.00, so the cell shows 20. Row 3, 5 of A100 at 2.50, shows 12.5.

The code is there but you still get #N/A

The usual cause is a space you cannot see, typed or pasted after the code ("A200 " is not "A200"). TRIM removes it before the lookup:

=XLOOKUP(TRIM(D2),A:A,B:B,"Not found")

The other common cause: codes that are numbers in one list and text in the other. Make both columns the same type (both numbers or both text) and the match works.

Older Excel without XLOOKUP

XLOOKUP is in Excel 2021 and later, Microsoft 365, Excel for the web and Google Sheets. In Excel 2019 or older, VLOOKUP gives the same result:

=VLOOKUP(D2,A:B,2,FALSE)

The 2 means "the second column of A:B" and FALSE means exact match. Leave out FALSE and VLOOKUP may return a wrong price without any error.

Watch it in 35 seconds

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.