Most lookups in Google Sheets ignore capital letters, which is usually helpful and sometimes a problem. If "abc" and "ABC" are different codes, VLOOKUP will quietly return the wrong one. This page lists which functions ignore case, which respect it, and the formulas that force either behaviour.
These treat "abc", "Abc" and "ABC" as the same:
VLOOKUP, HLOOKUP and MATCH
COUNTIF, COUNTIFS, SUMIF, SUMIFS
The = operator: ="abc"="ABC" returns TRUE
SEARCH (but see below for FIND)
EXACT(a, b) returns TRUE only when the text is identical, including case.
FIND(find, in) is case-sensitive: =FIND("ab","xAbc") gives #VALUE!, while =FIND("Ab","xAbc") gives 2. SEARCH does the same job ignoring case: =SEARCH("ab","xAbc") gives 2.
REGEXMATCH is case-sensitive by default: =REGEXMATCH("Abc","abc") is FALSE. Add (?i) at the start of the pattern to ignore case: =REGEXMATCH("Abc","(?i)abc") is TRUE. It also matches part of a cell, so anchor with ^ and $ for a whole-cell match.
Row 1 holds headers. Master list, D2:E4 (code, price):
abc, 1
ABC, 2
xyz, 3
Search values in A2:A4: ABC, abc, Abc. Results go in column B.
=VLOOKUP(A2,$D$2:$E$4,2,FALSE)
All three searches return 1, because the lookup ignores case and takes the first row that matches. For ABC, you wanted 2. It does not warn you.
Test each list item with EXACT, and ask MATCH for the first TRUE:
=IFNA(INDEX($E$2:$E$4,MATCH(TRUE,ARRAYFORMULA(EXACT($D$2:$D$4,A2)),0)),"Not found")
Fill down. The results are 2, 1 and Not found. "Abc" matches nothing exactly, so it ends up as Not found. The ARRAYFORMULA is needed so EXACT checks the whole list at once. IFNA turns a true no-match into text without hiding other errors.
FILTER can do the same without ARRAYFORMULA:
=IFNA(INDEX(FILTER($E$2:$E$4,EXACT($D$2:$D$4,A2)),1),"Not found")
It returns the same 2, 1, Not found. INDEX with 1 keeps the first row if several are identical.
COUNTIF ignores case. To count exact-case matches use SUMPRODUCT:
=COUNTIF($D$2:$D$4,"abc")
=SUMPRODUCT(--EXACT($D$2:$D$4,"abc"))
The first gives 2, the second gives 1.
Put the values Apple, apple, Apple, APPLE in A2:A5. A running count that respects case, in B2 filled down:
=SUMPRODUCT(--EXACT($A$2:A2,A2))
Results: 1, 1, 2, 1. A value greater than 1 marks a repeat of the exact same text. The case-ignoring version, =COUNTIF($A$2:A2,A2), gives 1, 2, 3, 4. To keep only the first exact-case copy, filter column B to 1, or use =FILTER(A2:A5,B2:B5=1).
UNIQUE is the odd one out: it is case-sensitive. With Apple, apple and APPLE in A2:A4:
=COUNTA(UNIQUE(A2:A4))
returns 3, because UNIQUE keeps each different capitalisation. COUNTUNIQUE(A2:A4) also returns 3. So a list can look duplicated to COUNTIF (which says Apple appears 3 times) and unique to UNIQUE at the same time. Force the behaviour you want:
To ignore case: =UNIQUE(LOWER(A2:A4)) returns a single value, apple, in lower case.
To respect case: UNIQUE already does, or use the Method 4 helper column and keep the rows where it equals 1.
The same thinking applies to Data > Data cleanup > Remove duplicates. Try it on a copy of three differently capitalised rows before trusting it on real data.
For FIND, use SEARCH, or wrap both sides: =FIND(LOWER("ab"),LOWER(A2)).
For EXACT, wrap both sides: =EXACT(LOWER(A2),LOWER(B2)) returns TRUE for "Abc" and "aBC".
For REGEXMATCH, start the pattern with (?i).
Matching "abc" with trailing spaces fails in both modes. Use TRIM first.
Extra spaces inside a value ("ab c") also break matches in both modes, so clean the data before comparing.
Case-sensitive formulas test every row of the list, so on very large ranges apply them only to the column you need.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
If your mismatches are typos or word order rather than capital letters, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas is built for that, but for case alone the formulas above are all you need.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.