# How to do a case-sensitive lookup in Excel

Look up TRUE in an EXACT comparison: `=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)`. Normal lookups ignore case.

**Excel:** `=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Code | Discount |  | Code | Save10 |
| SAVE10 | 0.1 |  | Discount |  |
| save10 | 0.05 |  |  |  |
| Save10 | 0.15 |  |  |  |
| WELCOME | 0.2 |  |  |  |
| welcome | 0.12 |  |  |  |
| VIP25 | 0.25 |  |  |  |

Formula in E2: `=XLOOKUP(TRUE,EXACT(A2:A7,E1),B2:B7)`

Result: 15%

## 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%.

## Other ways

- `=INDEX(B2:B7,MATCH(TRUE,EXACT(A2:A7,E1),0))`: Works in every Excel version (Ctrl+Shift+Enter in 2019 and earlier).
- `=VLOOKUP(E1,A2:B7,2,FALSE)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/case-sensitive-lookup