How to look up product codes with case-sensitive matching
Keep AB10 and ab10 as separate product codes and return the correct name for each. Check the matching rule and prevent extra matches caused by ignoring letter case.
If AB10 and ab10 identify different products, your lookup needs to distinguish uppercase and lowercase letters. JOIN SHEET does this by default. Join the code columns and leave Ignore letter case turned off to return the correct product name.
The matching rule comes from the source system
An ordinary VLOOKUP does not distinguish codes that differ only in case. If your product system uses that distinction, keep the original capitalization when preparing the lookup data.
The fictional Orders sheet contains ab10, AB10 and CD20. In the Product sheet, AB10 means Item A, ab10 means Item B, and CD20 means Item C. Each order should receive exactly one of these product names.
Keep case-sensitive matching enabled
- Load both sheets, read the code columns as text, and confirm that AB10 and ab10 identify separate products.
- Join Order code to Product code and select Matching rows only.
- Open Key value settings from the line menu. Leave Ignore letter case off, or turn it off if a previous task enabled it.
- Select Apply, then inspect the original codes and matched product names in Result preview.
Each code receives its own product name
| Order code | Product code | Product name |
|---|---|---|
| ab10 | ab10 | Item B |
| AB10 | AB10 | Item A |
| CD20 | CD20 | Item C |
Three orders produce three matches. If ab10 receives Item A or AB10 receives Item B, check the setting and the source codes. With Ignore letter case enabled, each of those two orders would match both products. Together with CD20, the result would have five rows.
When uppercase and lowercase mean the same product
Some systems use AB10 and ab10 for the same product. For those tasks, confirm the code rule and enable Ignore letter case. If both spellings occur in the Product sheet, a single order can still match multiple records. Run Check join and inspect the products. See how repeated keys multiply rows.
This setting applies to text join keys, not to separate Compare values lines. If the codes still fail to match because of extra spaces, check Ignore surrounding spaces. Check spelling errors and codes that only look similar in the source files.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Case sensitive vlookup or case sensitive if — Microsoft Q&A
The fictional products illustrate the sources' request for a lookup that distinguishes uppercase and lowercase codes.
