Case-Sensitive Lookup

All versionsEXACTINDEX/MATCH

VLOOKUP and XLOOKUP ignore case — “abc” matches “ABC.” When case matters (an ID like aB12 vs Ab12), pair EXACT with INDEX/MATCH to force an exact, case-sensitive match.

Quick formula: to look up the case-exact match for E2 and return column C:

=INDEX(C2:C8, MATCH(TRUE, EXACT(A2:A8, E2), 0))

EXACT compares each key to E2 case-sensitively, returning TRUE/FALSE; MATCH finds the TRUE; INDEX returns the paired value.

Functions used (tap for the full reference guide):

EXACT INDEX MATCH

The example

Two keys differ only in case. We look up aB12.

	A	B	D	E
1	Code	Price	Lookup	Price
2	AB12	$10	aB12	$25
3	aB12	$25
4	Ab12	$40

The formula

The case-exact price:

=INDEX(B2:B4, MATCH(TRUE, EXACT(A2:A4, "aB12"), 0)) // matches lowercase-a aB12 → $25

How it works

EXACT is what makes it case-sensitive:

EXACT(A2:A4, "aB12") compares every code to the lookup value respecting case, producing {FALSE, TRUE, FALSE}.
MATCH(TRUE, …, 0) finds the position of the TRUE — row 2.
INDEX(B2:B4, …) returns the price on that row — $25.
A plain VLOOKUP would have grabbed the first “ab12”-ish match regardless of case.

Array entry in old Excel. Because EXACT returns an array, Excel 2019 and earlier need this entered with Ctrl+Shift+Enter. Excel 365 and 2021 handle it natively.

Try it: interactive demo

Live demo

Pick which exact-case code to look up.

Lookup (E2)

Price:

Variations

Case-sensitive XLOOKUP (365)

Feed XLOOKUP an EXACT array:

=XLOOKUP(TRUE, EXACT(A2:A8, E2), C2:C8)

Case-sensitive count

How many cells exactly match:

=SUMPRODUCT(--EXACT(A2:A8, E2))

Two-key case-sensitive match

Multiply EXACT arrays:

=INDEX(C2:C8, MATCH(1, EXACT(A2:A8,E2)*EXACT(B2:B8,F2), 0))

Pitfalls & errors

VLOOKUP/XLOOKUP/MATCH are case-insensitive. None of them distinguish case on their own — EXACT is the only built-in case-sensitive comparison.

Old Excel needs Ctrl+Shift+Enter. The EXACT array won’t evaluate correctly as a normal formula before Excel 2021.

Trailing spaces still bite. EXACT also treats “aB12 ” (trailing space) as different. TRIM if needed.

Practice workbook

📊

Download the free Case-Sensitive Lookup practice workbook

Same-but-different-case codes with the live EXACT lookup (via SUMPRODUCT), the XLOOKUP and count variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I do a case-sensitive lookup in Excel?

Use INDEX/MATCH with EXACT: =INDEX(return, MATCH(TRUE, EXACT(lookup_range, value), 0)). EXACT compares respecting case, which VLOOKUP and XLOOKUP do not.

Why does VLOOKUP ignore case?

VLOOKUP, XLOOKUP, and MATCH are all case-insensitive by design. To distinguish case you must use the EXACT function, which is the only built-in case-sensitive text comparison.

Can XLOOKUP be case-sensitive?

Yes, indirectly: =XLOOKUP(TRUE, EXACT(lookup_range, value), return_range) uses an EXACT array so the match respects case.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: EXACT · INDEX · MATCH