Exact spreadsheet lookups: missing and duplicate keys
Use INDEX MATCH or exact VLOOKUP to return a price, then check missing and duplicate keys so a plausible result does not hide an ambiguous source table.
An exact lookup should return the value for one specific key. Check that the key exists once before relying on the result: a missing key and a duplicate key are different problems. The examples below use fictional product codes and department allowances.
How does INDEX MATCH retrieve a product price?
Put SKU codes in A11:A70, product names in B, and prices in C. Enter the selected SKU in B8.
| SKU | Product | Price in USD |
|---|---|---|
| SKU-001 | Example kit | 125 |
| SKU-002 | Example part | 18 |
| SKU-003 | Example accessory | 32 |
The formula =INDEX(C11:C70,MATCH(B8,A11:A70,0)) returns 18 when B8 is SKU-002. MATCH with zero requests an exact match and returns a position within the key range. INDEX uses that position in the price range. Microsoft illustrates this combination in its lookup reference.
In the INDEX MATCH price example, changing the selected SKU leaves the source table intact. You can compare the returned price with the matching record without rebuilding the formula.
How does exact VLOOKUP differ from INDEX MATCH?
For a department table with code in A, name in B, and allowance in C, =VLOOKUP(B8,A11:C70,3,FALSE) retrieves the third column for the exact code in B8. FALSE is significant: omitting the fourth argument requests approximate matching.
VLOOKUP searches the leftmost column of its table and uses a return-column number. INDEX MATCH uses separate key and result ranges. Either approach needs aligned source rows and an unambiguous key. Microsoft describes exact and approximate matching in Look up values with VLOOKUP, INDEX, or MATCH.
How do you expose duplicate lookup keys?
First calculate =COUNTIF(A11:A70,B8). Zero means the selected key is missing, one means it occurs once, and a larger count means the source is ambiguous for a one-record lookup.
Use =IF(COUNTIF(A11:A70,B8)=0,"Missing SKU",IF(COUNTIF(A11:A70,B8)>1,"Duplicate SKU",INDEX(C11:C70,MATCH(B8,A11:A70,0)))) to show those conditions explicitly. If multiple rows per key are intentional, use a conditional total or introduce a more specific key rather than silently accepting the first result.
The exact VLOOKUP workbook applies the same checks to department codes. Add a duplicate code and the selected allowance changes to a duplicate warning.
What should you check before using a lookup result?
Keep codes with leading zeros as text on both sides of the lookup. Remove unintended surrounding spaces consistently, preserving the original data when cleanup changes meaning. Check that the selected record’s result cell is populated: finding a key does not establish that its price or allowance has been entered.
Finally, test one known key, one absent key, and one duplicate key. Those three cases reveal more than a screenshot showing a single plausible price.