How to do a case-sensitive lookup
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Match 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 (desktop) | =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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
INDEX · MATCH · EXACT — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to look up the closest match (tax brackets, tiers, grades)
- How to find the most common text value (mode for text)
- How to find the label that goes with the highest value
- How to use INDEX/MATCH
- How to look up a value by both row and column (two-way lookup)