How to look up a price from a product code
Many workplace sheets have a reference list: product codes with prices, employee IDs with names, or postcodes with delivery zones. Other sheets need to pull information from that list. Copying the price by hand is slow, and it goes wrong when the price list changes. A lookup formula finds the code in the list and brings back the matching value for you.
The example
A supply store keeps its catalog in columns A to C. A customer asks about two codes, typed in F2 and F3. The prices should appear in G2 and G3.
| Row | A: Code | B: Product | C: Price | F: Requested code | G: Price | |
|---|---|---|---|---|---|---|
| 2 | P100 | Notebook | 4.50 | P300 | =XLOOKUP(F2, $A$2:$A$5, $C$2:$C$5, "Not found") → 12.00 | |
| 3 | P200 | Pen pack | 3.25 | P999 | → Not found | |
| 4 | P300 | Stapler | 12.00 | |||
| 5 | P400 | Desk tray | 14.00 |
Step by step with XLOOKUP
XLOOKUP needs to know four things. Read the formula as a sentence: “Look for this, in this list, and give me the value from this other list. If it isn’t there, say this.”
- Click G2 and type
=XLOOKUP(. - What to look for: the requested code,
F2. Type a comma. - Where to look: the code column,
$A$2:$A$5. Type a comma. - What to bring back: the price column,
$C$2:$C$5. It must cover the same rows as the code column. Type a comma. - What to show if it is missing:
"Not found", in quotation marks. - Close the parenthesis and press Enter. G2 shows 12.00, the stapler’s price. Copy the formula to G3. P999 is not in the catalog, so G3 shows “Not found.”
=XLOOKUP(F2, $A$2:$A$5, $B$2:$B$5, "Not found") returns “Stapler.”The older way: VLOOKUP
You will see VLOOKUP in many existing workplace files. The same lookup written with it is =VLOOKUP(F2, $A$2:$C$5, 3, FALSE). That means: look for F2 in the first column of A2:C5 and return the value from the 3rd column of that block. The FALSE at the end means “exact match only.” Leaving it off is a classic mistake, because VLOOKUP may then return the price of a similar code without warning. VLOOKUP can only search the leftmost column of the block. XLOOKUP does not have that limit, which is why newer guides prefer it.
Common mistakes
- Seeing
#N/A. This means “not found.” Check the code for typos and for hidden spaces. “P300 ” with a trailing space does not match “P300”. Adding the fourth “Not found” argument replaces the error with a clear message. - Numbers versus text codes. A code typed as the number 100 does not match the text “100”. If codes look identical but will not match, check whether one column is formatted as text.
- Lists of different lengths. If the code range is A2:A5 but the price range is C2:C4, XLOOKUP returns an error. Both ranges must start and end on the same rows.
- Ranges that slide when copied. Without dollar signs, copying the formula down shifts the catalog range down a row, and the first product drops out. Lock the catalog ranges with
$. - Missing FALSE in VLOOKUP. See above. Without it, a missing code can return a wrong price instead of an error.
Questions first-time users ask
Is XLOOKUP available in my spreadsheet program?
XLOOKUP works in Microsoft 365, Excel 2021 and later, Excel for the web, and Google Sheets. Older desktop versions such as Excel 2016 or 2019 do not have it, so use VLOOKUP there.
What happens if a code appears twice in the list?
XLOOKUP and VLOOKUP both return the first match, starting from the top. Duplicate codes in a catalog are usually a data problem worth fixing.
Can I hide other errors, such as dividing by zero?
Yes. IFERROR wraps any formula and shows your message instead of an error. For example, =IFERROR(B2/C2, "No orders") shows “No orders” when C2 is zero. The trainer’s Handle Empty Report Periods lesson practices this.
Practice it
What you will see in the trainer
The supply-store example above uses columns A:C and F:G. The Look Up Product Prices lesson has its catalog codes in A2:A4, prices in B2:B4, requested codes in D2:D3, and answers in E2:E3. Type =XLOOKUP(D2,A2:A4,B2:B4,"Not found") in E2, then use D3 in the E3 formula. The guided results are 40 and “Not found.”
In “Your turn,” the catalog changes. The same formulas give 70 for the code in D2 and “Not found” for the code in D3. You do not need dollar signs here because the trainer asks you to type each formula separately.
Related guides: preparing an invoice · counting order statuses · formulas used at work · all guides