How to do a case-sensitive lookup
✓ Verified in LibreOffice 25.8.7.3Match a code exactly, upper- and lower-case included — 'ABC' should not match 'abc'. VLOOKUP and XLOOKUP ignore case, so they can't tell these apart.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,"ABC"),0)) | EXACT compares case; MATCH(TRUE,...) finds the exact-case row. Older Excel: enter with Ctrl+Shift+Enter. 365: =XLOOKUP(TRUE,EXACT(A2:A5,"ABC"),B2:B5). |
| Google Sheets | =INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,"ABC"),0)) | Identical. XLOOKUP(TRUE,EXACT(A2:A5,"ABC"),B2:B5) also works. |
| LibreOffice Calc | =INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,"ABC"),0)) | Identical; array-enter in older builds. |
How it works
VLOOKUP, HLOOKUP, XLOOKUP and a plain MATCH all treat 'abc', 'ABC' and 'Abc' as the same string, so they return whichever appears first — wrong when the case IS the identifier (SKUs, tokens, passwords). EXACT(A2:A5,"ABC") is the fix: it compares each cell character-by-character including case, producing {FALSE;TRUE;FALSE;FALSE}. MATCH(TRUE, that array, 0) finds the position of the genuine match (row 2), and INDEX returns the paired value from column B — here 2. In legacy Excel this is a Ctrl+Shift+Enter array formula; in Excel 365 and Google Sheets XLOOKUP(TRUE,EXACT(...),...) reads more cleanly. Add more EXACT conditions multiplied together for a case-sensitive multi-column key.
Verified, not just documented
We ran =INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,"ABC"),0)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2 — exactly the expected result. Every formula here is confirmed by actually executing it.