How to do case-sensitive matching between two lists
By the Analistable team · Updated · 5 min read
VLOOKUP, XLOOKUP, MATCH and COUNTIF all ignore case. For a case-sensitive match, compare with EXACT, which returns TRUE only when case matches too: =XLOOKUP(TRUE, EXACT(List!A2:A500, F2), List!B2:B500, "no match"). In SQL, a plain = is case-sensitive in many engines, including DuckDB and PostgreSQL. If case differences are accidents, normalise both lists with UPPER or LOWER instead.
Part of our guide: How to compare two spreadsheets for differences and matches
When case matters — and when it doesn't
Most of the time, case differences between lists are noise: "harper ltd" typed in one system and "Harper Ltd" in another are the same customer, and Excel's case-insensitive lookups are helpful. But some identifiers are case-sensitive by design:
- Short codes and tokens where
aB3xandAb3Xare different records (URL shorteners, voucher codes, some system IDs). - Salesforce 15-character record IDs, which are case-sensitive (the 18-character form was added so case-insensitive tools could tell them apart).
- Product codes where suffix case means a variant, such as size
sversusSin some catalogues. - Passwords, hashes and base64 strings — rarely in a spreadsheet, but when they are, case is everything.
Decide which situation you're in before choosing a method. Matching case-sensitively on data that should be case-insensitive creates false misses; the reverse merges different records.
Which functions ignore case
| Function or tool | Case-sensitive? |
|---|---|
| VLOOKUP, HLOOKUP, XLOOKUP, MATCH, XMATCH | No |
| COUNTIF, SUMIF, COUNTIFS | No |
| = comparison in a cell (=A2=B2) | No |
| Remove Duplicates (Excel) | No |
| EXACT, FIND, SUBSTITUTE | Yes |
| Power Query Merge (default) | Yes |
| SQL = in DuckDB, PostgreSQL, SQLite (default) | Yes |
| SQL = in MySQL, SQL Server (typical default collation) | Usually no |
SQL behaviour depends on collation, which a database administrator can change. Check yours before relying on the default.
Case-sensitive lookup with EXACT
EXACT compares two texts and returns TRUE only if every character, including case, is identical. Given a range, it returns an array of TRUE/FALSE, and you look up TRUE in that array. In Microsoft 365 or Excel 2021:
=XLOOKUP(TRUE, EXACT(Codes!A2:A500, F2), Codes!B2:B500, "no match")To get the position instead of a value:
=XMATCH(TRUE, EXACT(Codes!A2:A500, F2))In older Excel, use INDEX/MATCH and confirm with Ctrl+Shift+Enter if your version requires array entry:
=INDEX(Codes!B2:B500, MATCH(TRUE, EXACT(Codes!A2:A500, F2), 0))To check whether each item in list 1 exists in list 2 with exactly the same case:
=SUMPRODUCT(--EXACT(List2!$A$2:$A$500, A2)) > 0In Google Sheets the same EXACT approach works, and =FILTER(Codes!B2:B500, EXACT(Codes!A2:A500, F2)) returns every case-exact match rather than the first.
Worked example: voucher codes
| Redeemed | XLOOKUP (ignores case) | EXACT lookup | Issued to |
|---|---|---|---|
| aB3x | Match → Priya | Match | Priya |
| Ab3X | Match → Priya (wrong) | Match | Tom |
| kQ9z | Match → Lena | Match | Lena |
| KQ9Z | Match → Lena (wrong) | No match | — |
The issued list contains aB3x (Priya), Ab3X (Tom) and kQ9z (Lena). A normal lookup gives Priya for both of the first two codes, because aB3x is listed first, and accepts KQ9Z, which was never issued. EXACT gets all four right: 3 genuine redemptions and 1 invalid code.
Case-sensitive matching in SQL
-- DuckDB / PostgreSQL: = already compares case-sensitively
SELECT r.code, i.issued_to
FROM redeemed r
LEFT JOIN issued i ON r.code = i.code;
-- Case-insensitive alternative, when case is noise
SELECT r.code, i.issued_to
FROM redeemed r
LEFT JOIN issued i ON lower(r.code) = lower(i.code);Analistable runs queries in DuckDB in your browser, where = is case-sensitive. That makes it easy to do either: ask for an exact-case match, or ask it to compare in lower case when the difference is just typing. The SQL is shown, so you can see which it did.
Case-sensitive comparison of two whole lists
To flag every item in list 1 that has no case-exact twin in list 2, add a status column next to list 1:
=IF(SUMPRODUCT(--EXACT(List2!$A$2:$A$500, A2)) > 0, "in both", "only in list 1")Fill it down and filter for "only in list 1"; then run the same formula from the other side to find items only in list 2. In Microsoft 365 you can produce the whole list of missing items in one formula:
=FILTER(A2:A500, MAP(A2:A500, LAMBDA(x, SUM(--EXACT(List2!A2:A500, x)) = 0)))MAP and LAMBDA need a recent Microsoft 365 build. SUMPRODUCT with EXACT compares each item against the whole other list, so on two lists of tens of thousands of rows it gets slow; at that size, Power Query or SQL is the better tool.
Case-sensitive duplicates within one list
Excel's Remove Duplicates and COUNTIF both treat "aB3x" and "AB3X" as the same, so they'll report a duplicate that isn't one. To count case-exact copies of each value, use =SUMPRODUCT(--EXACT($A$2:$A$500, A2)); a result above 1 is a true duplicate. In SQL, GROUP BY code HAVING COUNT(*) > 1 in DuckDB groups case-sensitively by default, while GROUP BY lower(code) finds values that differ only by case — useful for spotting near-collisions before you decide how to treat them.
When to normalise instead
If case differences are accidents, the better fix is to make both lists consistent and then use ordinary lookups. Add a helper column with =UPPER(TRIM(A2)) on each list, or in Power Query use Transform → Format → UPPERCASE on both key columns before merging — important because Power Query merges are case-sensitive and will otherwise miss matches that Excel formulas would find.
Normalising also makes results reproducible across tools: the same lists give the same matches in Excel, Power Query and SQL. For more on list matching, see find matches between two lists in Excel and comparing two columns; the compare spreadsheets guide covers wider comparisons.
Frequently asked questions
- Is XLOOKUP case-sensitive?
- No. XLOOKUP, VLOOKUP and MATCH all ignore case. Wrap the lookup array in EXACT and look up TRUE to make the match case-sensitive.
- How do I compare two cells case-sensitively?
- Use =EXACT(A2, B2). A plain =A2=B2 ignores case.
- Is Power Query merge case-sensitive?
- Yes, by default. 'abc' and 'ABC' don't match in a standard merge. Convert both key columns to the same case first, or use fuzzy merge with its ignore-case option.
- Can COUNTIF count case-sensitively?
- No. Use =SUMPRODUCT(--EXACT(range, value)) to count case-exact occurrences.
- Should I match case-sensitively by default?
- Only for identifiers that are case-sensitive by design, such as voucher codes or some system IDs. For names, emails and most product codes, normalise case and match case-insensitively.