The task
Use the product list to find the price for each requested code. Keep the request order. A code that does not exist should say NOT FOUND, not zero.
Download the practice kit · 3 KB →
This is original fictional data. The ZIP contains two CSV files, expected answers, instructions and a Python reference solution. On a phone, you can solve the four rows below without downloading anything.
Swipe to compare
| Code | Product | Price |
|---|---|---|
| P101 | Notebook | 3.50 |
| P102 | Pen | 1.20 |
| P103 | Folder | 2.00 |
| P104 | Marker | 1.80 |
Find these, in this order: P103, P101, P999, P102. Try before opening the answer.
Check your result
Show the four answers
- P103 → 2.00
- P101 → 3.50
- P999 → NOT FOUND
- P102 → 1.20
P999 is deliberately absent. A price of zero would mean something different: a real product that costs nothing.
Try it in Excel
- Open products.csv and name its sheet Products. Keep the header in row 1, codes in column A and prices in column C.
- Put the four codes from requests.csv in cells A2:A5 of a second sheet.
- Enter the formula below in B2 and fill down to B5. Set numeric results to two decimal places.
=XLOOKUP(A2,Products!$A$2:$A$5,Products!$C$2:$C$5,"NOT FOUND",0)
The final 0 requests an exact match. The dollar signs keep the product ranges fixed when you fill down. If your regional settings use semicolons between arguments, replace the commas.
XLOOKUP is not available in Excel 2016 or 2019. Microsoft's function guide lists supported versions and explains the arguments.
The harder case: two prices for P101
Add a second P101 in row 6 of Products, with a different price. Expand your lookup range to row 6. A simple lookup can return the first match and hide the conflict. For this exercise, we want DUPLICATE CODE instead.
=IF(COUNTIF(Products!$A$2:$A$6,A2)>1,"DUPLICATE CODE",XLOOKUP(A2,Products!$A$2:$A$6,Products!$C$2:$C$6,"NOT FOUND",0))
Now P101 should flag the duplicate while P999 remains missing. These are different errors and should not share the same answer.
Or use Python after a CSV lesson
Read both CSVs with Python's built-in csv module. Match the codes and print code,price,status in the request order. Keep the missing price empty and put NOT FOUND in the status column.
After trying, open reference.py. Run it with Python 3 from the extracted folder. It reads the files beside itself and prints the result; it does not upload your data or install anything.
The reference handles missing and duplicate codes. Our local checks cover the original answers, repeated requests and the duplicate extension. The Excel formulas have been checked against Microsoft's syntax and the sample data; this update did not run the Excel app.
If you get stuck
Ask for a hint about the exact step: “My formula finds P101, but P999 shows an error. Explain how the missing-value argument works without solving the other rows.” Try the change in your file before treating the explanation as correct.
For a lesson first, use our Excel channel or course guide. Once you can solve these rows, repeat a request and add a new product to see whether your solution still behaves properly.
