Find Any Product Price in Google Sheets with XLOOKUP
Enter a product ID and return its price with an exact match.
Download practice data · CSVStart the exerciseWatch the tutorial
Watch on YouTube · ShortTry it yourself
Import at A1. Type P-103 in D2, then put the formula in E2. Expect 249. Try an unknown ID to see Not found. Duplicate IDs return the first match.
=XLOOKUP(D2,A2:A6,B2:B6,"Not found",0)Detailed steps
- 1. Keep the lookup table simple
In this fictional catalog, column A contains product IDs and column B contains prices. The data runs from row two to row six. Keep each product ID unique.
- 2. Add an input and output
Type the product ID you want in D two. Here it is P one oh three. Click E two for the price result. Keeping the input separate makes the formula reusable.
- 3. Enter XLOOKUP
Enter XLOOKUP with D two as the search key. Next use A two through A six for the IDs, and B two through B six for prices. Add Not found in quotation marks, then zero for an exact match.
- 4. Read the matching result
Press Enter. The result is two hundred forty nine dollars, from the row for P one oh three. The lookup and result ranges must have matching lengths. The source table stays unchanged.
- Two checks before using it
A missing ID returns our Not found message. Duplicate IDs can return the first match, so check your catalog. Also watch extra spaces in imported IDs. Which lookup should we build next: prices, stock, or customer details?
Preview the input data
| Product ID | Price (USD) |
|---|---|
| P-101 | 49 |
| P-102 | 25 |
| P-103 | 249 |
| P-104 | 35 |
| P-105 | 79 |
How to open the CSV
In a blank Google Sheet choose File → Import → Upload and select the downloaded CSV. Use comma as the separator. Start at A1 unless the exercise specifies another location. CSV contains input data; add formulas and controls using the steps above. Files are hosted directly here and require no shared personal Drive document.
Fictional demo data. Formulas use English function names and commas between arguments. Other spreadsheet locales may require semicolons. Check date recognition after importing.