Lookup & Reference
Case-sensitive lookup with EXACT, XLOOKUP and INDEX/MATCH
Use a per-row EXACT helper to distinguish letter case in text identifiers, with explicit blank and missing-match handling.
Standard XLOOKUP, VLOOKUP, and MATCH ignore uppercase vs lowercase!
Ordinary exact-match XLOOKUP and MATCH compare text without distinguishing letter case. EXACT supplies a case-sensitive text comparison for each row; the helper is 1 for a match and 0 otherwise. This does not establish cryptographic or byte-level Unicode identity.
This exercise requires a nonempty text target and text source identifiers. It preserves spaces and punctuation rather than trimming or normalizing them. Duplicate exact-case identifiers return the first matching row. A numeric identifier should be stored deliberately as text if its textual form matters.
| Code (Col A) | Price (Col B) | EXACT Flag vs "TX-10" | Lookup Behavior for "TX-10" |
|---|---|---|---|
| tx-10 | $120 | 0 (case mismatch) | Ordinary case-insensitive lookup returns the first matching row (120) |
| TX-10 | $275 | 1 (strict case match!) | Case-sensitive XLOOKUP(1, C2:C4, ...) -> $275! |
| Tx-10 | $410 | 0 (case mismatch) | Matched only when E2 = "Tx-10" |
Case-Sensitive XLOOKUP Against an EXACT Helper Column
Each helper checks a nonempty text target and a text source before EXACT. The lookup returns the first 1. Enter each helper formula in C2:C4 or use the prepared example.
=XLOOKUP(1, C2:C4, B2:B4, "No Exact-Case Match")INDEX/MATCH with an explicit missing-position guard
IFNA converts a missing MATCH position to zero. Check that position before calling INDEX; IFNA around the entire INDEX expression can hide an actual source error and is not needed here. This formula uses scalar helpers rather than an inline EXACT array.
=IF(IFNA(MATCH(1,C2:C4,0),0)=0,"No Exact-Case Match",INDEX(B2:B4,MATCH(1,C2:C4,0)))Try the example
Use the original values or change them to test your understanding.