How to do a case-sensitive lookup in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)Look up TRUE in an EXACT comparison: =XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7). Normal lookups ignore case.
- Result on the example
- 15% in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
EXACT compares two pieces of text and is TRUE only when every character matches, including case. Applied to A2:A7, it returns one TRUE, on the "Save10" row. XLOOKUP finds that TRUE and returns 15%.
=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)Result0.15
A2:A76 rows × 1 col: "SAVE10", "save10", "Save10", "WELCOME", …A range: 6 cells in one column.
E1"Save10"The value in E1.
EXACT(A2:A7,E1){6 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …EXACT: TRUE if two texts match exactly, including case.- text1
A2:A76 rows × 1 col: "SAVE10", "save10", "Save10", "WELCOME", … - text2
E1"Save10"
- text1
B2:B76 rows × 1 col: 10%, 5%, 15%, 20%, …A range: 6 cells in one column.
XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)0.15XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
EXACT(A2:A7,E1){6 rows × 1 col} FALSE, FALSE, TRUE, FALSE, … - return_array
B2:B76 rows × 1 col: 10%, 5%, 15%, 20%, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7). We ran it in Google Sheets mode on the same data and it returns 15%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:B7,MATCH(TRUE,EXACT(A2:A7,E1),0)) | 15% | Works in every Excel version (Ctrl+Shift+Enter in 2019 and earlier). |
| =VLOOKUP(E1,A2:B7,2,FALSE) | 10%Different result | Shown for contrast: VLOOKUP ignores case and stops at the first "save10" in any case. Returns 10%, the discount for SAVE10. |
Common mistakes
- VLOOKUP, XLOOKUP, MATCH and COUNTIF all ignore case. Only EXACT and FIND tell "A" and "a" apart.
- EXACT also treats a trailing space as a difference, so "Save10 " won't match.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Do a case-sensitive lookup
E2, return the discount for the code in E1, matching upper and lower case exactly.Questions
How do I count case-sensitive matches?
Sum the EXACT results: =SUMPRODUCT(--EXACT(A2:A7,E1)).
Does this work in Google Sheets?
Yes. EXACT and XLOOKUP work the same way there.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.