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).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.