Module 04: Connect and summarize data
Look up a product with an explicit exact match
Retrieve a name and price by product ID, handle a missing ID, and recognize the limits of a first-match result.
Excel for Microsoft 365, Excel 2021, and Excel 2024 for Windows desktop. XLOOKUP is not available in Excel 2016 or 2019; use the INDEX/MATCH lesson for those versions. English formulas use commas; some locales require semicolons. Time is an estimate.
Useful first: Copy formulas while keeping shared inputs fixed · Clean hidden spaces without losing the original text
- Align a lookup column with its return column.
- Use an explicit exact-match mode and a readable missing-key result.
- Investigate duplicate keys instead of assuming one returned row is authoritative.
Small data. A result you can check.
Synthetic three-product catalog. Paste at A1. Add any duplicate only during the separate exercise.
View the raw practice data
ProductID Name Price P101 Blue Mug 12 P102 Tray 8 P103 Cup 5
01Separate the key from the information you want back
Paste the product sample into A1:C4: product keys in A, names in B, prices in C. Enter ProductID in E1, P102 in E2, Name in F1, and Price in G1. The requested result is Tray and 8.
The prefixed sample IDs avoid numeric-versus-text ambiguity. Real leading-zero or long digit-only IDs need text-preserving import; lookup cannot restore characters already lost.
| Range | Role |
|---|---|
| A2:A4 | Search these product IDs |
| B2:B4 | Return the corresponding name |
| C2:C4 | Return the corresponding price |
| E2 | Product ID requested by the user |
02Enter the exact-match formulas
Enter the formula below in F2. Its final 0 explicitly requests an exact match, although that is also XLOOKUP's default. The Not found argument is the result for a missing key; it is not a substitute for every possible formula error.
In G2 enter =XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"Not found",0). The lookup and return arrays cover the same three records. Keep the ranges aligned and use absolute references for this fixed catalog. These examples use the default forward search and do not require sorting the source.
=XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"Not found",0)Enter in F2. The product P102 returns Tray; the parallel price formula in G2 returns 8.
03A first match does not prove a key is unique
If two catalog records have the same key, a normal forward XLOOKUP returns the first match. That can conceal a conflicting price rather than resolve it. Before relying on the result, check the source's key policy and review repeated identifiers.
As a controlled demonstration, add P102, Revised Tray, and 9 in A5:C5 and extend both formula ranges to row 5. The forward search still returns Tray and 8. A reverse search can choose the last occurrence, but the last row is not automatically the latest or correct record. Resolve the conflict using a documented source rule.
Check successful, missing, and duplicated product IDs on a working copy.
- Before adding the duplicate demonstration, change E2 to P103, then P999. Observe both return cells each time.
- Restore E2 to P102. Add the duplicate row described above, extend A/B/C formula ranges to row 5, and compare the conflicting source prices.
- Remove only the synthetic duplicate row you added and restore ranges to row 4; confirm the original catalog is intact.
Show the worked answer
P103 returns Cup and 5; P999 returns Not found in both cells.
With the duplicated P102 included, the default forward formulas still return the first catalog entry, Tray and 8.
The final restored state contains three products and P102 returns Tray and 8.
Check your work- E2 and the source keys use the same text values without added spaces.
- Lookup and return ranges start and end on the same source rows.
- A displayed price is not treated as proof that the source key is unique.
One question before you move on.
Ready for the next step?
Mark this lesson when you can explain the idea and reproduce the practice result.
Your checklist stays on this browser.Reference notes
These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.
Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.