Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to do a case-sensitive lookup in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,D2),0))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Excel, enter the formula =INDEX(B2:B5,MATCH(TRUE,EXACT(A2:A5,D2),0)) and confirm with Ctrl+Shift+Enter to make it an array formula.
- 2In Google Sheets, enter the same formula and press Enter normally — it handles arrays automatically.
- 3EXACT compares each lookup column value to D2 with case sensitivity, returning TRUE only for an exact case match.
- 4MATCH finds the first TRUE, and INDEX returns the corresponding value.
Tips
- In Excel 365, you can use =XLOOKUP(D2,A2:A5,B2:B5) — but note XLOOKUP is also case-insensitive. The INDEX/MATCH/EXACT approach remains the reliable case-sensitive method in both apps.
- If multiple rows match with the same case, the formula returns the first match. For the last match, use LOOKUP(2,1/EXACT(A2:A5,D2),B2:B5).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why does VLOOKUP not distinguish case?
VLOOKUP uses a case-insensitive comparison internally. 'apple', 'Apple', and 'APPLE' are all treated as the same string.
How does EXACT differ from a normal equals comparison?
EXACT(a,b) returns TRUE only when the text and its case match exactly. The = operator in formulas is case-insensitive.
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to look up a value and sum the matches
Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.
How-toHow to look up a value across multiple columns
Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.