Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the Case mismatch error
Excel & Google SheetsWhat Case mismatch means
Standard lookup and comparison functions in both Excel and Google Sheets are case-insensitive — 'Apple', 'apple', and 'APPLE' are treated as identical. However, a few functions like EXACT() and FIND() are case-sensitive. If your data has inconsistent casing and you are using EXACT-based logic, a case mismatch can cause failures.
Common causes
- EXACT() is used in a comparison formula where case should not matter, and the data has mixed casing (e.g., 'North' vs 'NORTH').
- A helper column or lookup key was created by concatenating values from different sources that used different case conventions.
- Data from two systems uses different capitalization standards (e.g., a CRM exports 'NEW YORK' while a spreadsheet has 'New York').
- FIND() (case-sensitive) was used instead of SEARCH() (case-insensitive) to locate a substring, causing misses on differently-cased values.
- A user manually types a lookup value in a different case from what is in the table, and a downstream formula was written expecting an exact case match.
Example fix
Broken
=IF(EXACT(A2,"apple"),"Match","No match")Fixed
=IF(LOWER(A2)="apple","Match","No match")EXACT() is case-sensitive by design, so 'Apple' and 'Apple' match but 'Apple' and 'apple' do not. Replacing EXACT with a LOWER() comparison makes the check case-insensitive, so 'Apple', 'APPLE', and 'apple' all return 'Match'.
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
How to fix it
- 1For standard lookups: do nothing — VLOOKUP, MATCH, IF, and COUNTIF are already case-insensitive. Recheck whether case is actually the issue by confirming with EXACT(A2,B2).
- 2Normalize casing in both the lookup value and the lookup table before comparing. Use LOWER() on both sides: =VLOOKUP(LOWER(A2),LOWER($D:$D),2,0) (requires array entry). Or use UPPER() consistently.
- 3Clean the source data: use a helper column with =PROPER(A2), =UPPER(A2), or =LOWER(A2) to standardize casing across the column, paste values, then delete the helper.
- 4If you need a truly case-sensitive lookup (e.g., finding 'USD' but not 'usd'), use an array formula: =INDEX($E:$E,MATCH(TRUE,EXACT($D:$D,A2),0)) — entered with Ctrl+Shift+Enter in Excel or as a regular formula in Google Sheets.
- 5Replace FIND() with SEARCH() in formulas that should be case-insensitive substring searches.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every Case mismatch and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
Is VLOOKUP case-sensitive?
No. VLOOKUP, HLOOKUP, MATCH, IF, COUNTIF, SUMIF, and INDEX are all case-insensitive in both Excel and Google Sheets. 'apple' and 'APPLE' match in all of these functions.
How do I do a case-sensitive VLOOKUP?
Use an array formula: =INDEX($B:$B,MATCH(TRUE,EXACT($A:$A,A2),0)). In Excel, press Ctrl+Shift+Enter. In Google Sheets, enter it as a normal formula — it evaluates as an array automatically.
Which functions in Excel are case-sensitive?
EXACT(), FIND(), and FINDB() are case-sensitive. All other common text and lookup functions (SEARCH, VLOOKUP, MATCH, IF, SUBSTITUTE, COUNTIF) are case-insensitive.