Excel thinks “ABC123” and “abc123” are the same. Usually that’s helpful, but not when product codes, passwords, IDs or part numbers depend on capital letters. Here’s how to make lookups case-sensitive using the EXACT function.
The problem
| A | B | |
|---|---|---|
| 1 | Code | Product |
| 2 | ab-10 | Blue pen |
| 3 | AB-10 | Black marker |
| 4 | Ab-10 | Red pencil |
=XLOOKUP("AB-10", A2:A4, B2:B4)Result: Blue pen ❌ (the first match, ignoring case). VLOOKUP, XLOOKUP, MATCH, COUNTIF and SUMIF all ignore case.
Meet EXACT
=EXACT(text1, text2)Returns TRUE only if both texts are identical, including upper and lower case.
=EXACT("AB-10","AB-10")→ TRUE=EXACT("AB-10","ab-10")→ FALSE
Case-sensitive XLOOKUP
=XLOOKUP(TRUE, EXACT(A2:A4, "AB-10"), B2:B4, "Not found")Result: Black marker ✅
How it works: EXACT(A2:A4,"AB-10") checks every code and returns {FALSE; TRUE; FALSE}. XLOOKUP then finds the first TRUE and returns the product from that row.
Use a cell instead of typing the code: =XLOOKUP(TRUE, EXACT(A2:A4, E1), B2:B4, "Not found")
Older Excel: INDEX and MATCH
=INDEX(B2:B4, MATCH(TRUE, EXACT(A2:A4, E1), 0))In Excel 2019 or earlier, press Ctrl + Shift + Enter to confirm it. See INDEX and MATCH explained.
Count case-sensitive matches
COUNTIF ignores case, so use SUMPRODUCT with EXACT:
=SUMPRODUCT(--EXACT(A2:A100, E1))The -- turns TRUE/FALSE into 1/0 so they can be added up.
Sum case-sensitive matches
=SUMPRODUCT(EXACT(A2:A100, E1)*C2:C100)Adds up column C only where the code matches exactly.
Find case-sensitive duplicates
Put this in a helper column next to your list:
=IF(SUMPRODUCT(--EXACT($A$2:$A$100, A2))>1, "Duplicate", "")Excel’s built-in Remove Duplicates ignores case, so this helps when “AB-10” and “ab-10” are truly different items.
Return all case-sensitive matches
=FILTER(A2:C100, EXACT(A2:A100, E1), "None")Learn more in UNIQUE, SORT and FILTER.
Other case-sensitive functions
| Case-sensitive | Not case-sensitive |
|---|---|
| EXACT, FIND, SUBSTITUTE | VLOOKUP, XLOOKUP, MATCH, COUNTIF, SUMIF, SEARCH, the = operator |
For example, =ISNUMBER(FIND("AB",A2)) checks if A2 contains capital “AB”, while SEARCH would also match “ab”.
Google Sheets
EXACT works the same way. In Google Sheets, wrap array formulas in ARRAYFORMULA() if needed:
=XLOOKUP(TRUE, ARRAYFORMULA(EXACT(A2:A4, E1)), B2:B4)Quick summary
- Most lookup functions ignore upper/lower case.
- Use
=XLOOKUP(TRUE, EXACT(range, value), return_range)for a case-sensitive lookup. - Use
SUMPRODUCT(--EXACT(...))to count, and FILTER + EXACT for all matches.