The wrong column is returned
The third argument should be the Price range, not the product name or the ID range.
Look up a price by an exact ID
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill G2:G4 with the price for each ID in F2:F4. Use an exact match, return the first match, and show Not found when no ID matches.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G2, G3, G4. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Enter a product ID, return its price, and handle missing or repeated IDs without hiding the rule.
Choose the result you need
F2 supplies the ID, A2:A7 contains IDs and C2:C7 contains prices. Match mode 0 requests an exact match; the missing-result argument returns readable text. Fill G2:G4.
=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0)Exact matching ignores letter case here. Keep the lookup and return ranges aligned. This example returns one price, not all matching rows or an approximate price band.
All result formulas
- G2
=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0)- G3
=XLOOKUP(F3,$A$2:$A$7,$C$2:$C$7,"Not found",0)- G4
=XLOOKUP(F4,$A$2:$A$7,$C$2:$C$7,"Not found",0)
| Row | A | B | C | F | G |
|---|---|---|---|---|---|
| 1 | "Product ID" | "Product" | "Price" | "Find ID" | "Price found" |
| 2 | "P-101" | "Notebook" | 8 | "P-103" | 5 |
| 3 | "P-102" | "Pen set" | 12 | "p-105" | 9 |
| 4 | "P-103" | "Folder" | 5 | "P-999" | "Not found" |
| 5 | "P-104" | "Desk pad" | 18 | — | — |
| 6 | "P-105" | "Binder" | 9 | — | — |
| 7 | "P-106" | "Clips" | 4 | — | — |
These are reference examples with matching downloads. The primary worksheet below has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practice ↓Microsoft function reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
How it works
Duplicate IDs need a policy
This formula searches from top to bottom and returns the first match. It does not warn you when an ID appears twice. Check for duplicates before using a catalogue where IDs are expected to be unique.
Exact does not mean case-sensitive
Text IDs are matched without regard to letter case. Number 7 and text "007" are different IDs. Keep identifier types consistent and do not strip meaningful leading zeros.
Version and scope
XLOOKUP is not available in every older version of Excel. This lesson uses exact matches on a small table, not approximate tiers or wildcard searching. Compatibility with this browser engine, Microsoft Excel and Google Sheets is checked separately.
Read the full step-by-step walkthrough
- Choose the search value
F2 contains the ID to find. Keep it separate from the catalogue so it can be changed easily.
F2 - Search the ID column
Only product IDs belong in the lookup array. Both arrays start and end on the same rows.
$A$2:$A$7 - Return the corresponding price
The return array is the Price column. A match on row five returns the value on row five of this range.
$C$2:$C$7 - Make a missing ID readable
The fourth argument provides a clear response when nothing matches. A real zero price remains the number zero.
"Not found" - Choose exact matching and fill down
The fifth argument is 0. Lock the catalogue ranges and let F2 move to F3 and F4 when copied.
0
Things to watch
A missing ID returns a nearby item
Approximate matching answers a different question. Use exact match mode 0 for identifiers.
A repeated ID returns the later value unexpectedly
This task expects the first match. If your data needs the last or all matches, define that rule separately.
About this example
Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.
Content version 2. Practice checks compare calculated results across different datasets; they do not certify every possible formula.