Case-Sensitive Matching in Excel With EXACT

The Excel EXACT function compares two values and returns TRUE only when they are identical, capital letters included. Most of Excel ignores case: ="ab-1"="AB-1" is TRUE, and VLOOKUP treats them as the same code. This guide shows how to use EXACT on its own, inside IF, and in case-sensitive counts and lookups, for anyone working with codes, passwords-style IDs or SKUs where capitals carry meaning.

What does EXACT do in Excel?#

EXACT checks whether two texts match character for character and returns TRUE or FALSE.

=EXACT(text1, text2)

EXACT works in every version of Excel. It ignores formatting such as bold or colour, but it doesn't ignore spaces.

Why = and VLOOKUP aren't case-sensitive#

Excel's normal comparisons treat capital and small letters as equal. This is usually helpful: "Leeds", "LEEDS" and "leeds" should all count as the same city. The comparison operators guide covers how =, <> and the others work on text.

The functions that rely on those comparisons ignore case too:

• the = and <> operators;
• VLOOKUP, HLOOKUP, XLOOKUP and MATCH;
• COUNTIF, SUMIF and their IFS versions.

That becomes a problem when capitals carry meaning, as in product codes where "AB" and "ab" are different items, or in case-sensitive system IDs. EXACT is the tool for those cases.

How to use EXACT to check typed values#

Compare each typed value with the master value to find entries in the wrong case:

In C2, type =EXACT(A2,B2) and fill down with Ctrl+D (Windows) or ⌘D (Mac). Row 4 fails because of a trailing space (shown as ␣), not the capitals. If spaces shouldn't count, compare =EXACT(TRIM(A4),TRIM(B4)) instead.

How to use EXACT inside IF#

EXACT returns TRUE or FALSE, so it fits straight into the test of an IF. The IF function guide explains the three parts.

=IF(EXACT(A2,B2),"OK","Check case")

This shows "OK" for an exact match and "Check case" for anything else. You can also check that a cell is all capitals by comparing it with its own UPPER version:

=IF(EXACT(A2,UPPER(A2)),"OK","Use capitals")

For "Pr-8810" this returns "Use capitals". The same test works as a data validation rule: select the cells starting from A2, choose Data > Data Validation, set Allow to Custom, and enter =EXACT(A2,UPPER(A2)) as the formula. Excel then rejects any entry that isn't in capitals. Our IF function article has more ways to turn a TRUE/FALSE test into a message.

How to count case-sensitive matches#

COUNTIF ignores case, so use SUMPRODUCT with EXACT:

=SUMPRODUCT(--EXACT(A2:A10,"AB-1042"))

How it works:

1. EXACT(A2:A10,"AB-1042") checks every cell and returns a list of TRUE and FALSE.
2. The double minus -- turns TRUE into 1 and FALSE into 0.
3. SUMPRODUCT adds the 1s.

If the range holds "AB-1042" twice and "ab-1042" once, COUNTIF returns 3 but this formula returns 2. SUMPRODUCT works in every version of Excel without Ctrl+Shift+Enter.

How to do a case-sensitive lookup#

Ask XLOOKUP to find the first TRUE in a list of EXACT results:

=XLOOKUP(TRUE,EXACT(A2:A6,E2),B2:B6)

EXACT compares E2 with every code in A2:A6 and returns TRUE only where the case matches. XLOOKUP then finds that TRUE and returns the value from B2:B6 on the same row. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365.

In older versions, use INDEX and MATCH the same way:

=INDEX(B2:B6,MATCH(TRUE,EXACT(A2:A6,E2),0))

In Excel 2019 and older, confirm this one as an array formula with Ctrl+Shift+Enter, because it handles a whole range inside MATCH. In Excel 2021, Excel 2024 and Microsoft 365 a normal Enter is enough.

How to find duplicates that differ only in case#

COUNTIF can't tell "AB-1042" from "ab-1042", so a normal duplicate check counts them as the same code. To flag only true, case-exact duplicates, count each value with EXACT against the whole column:

=SUMPRODUCT(--EXACT($A$2:$A$10,A2))>1

Fill it down next to the list. It returns TRUE when the exact same text, capitals included, appears more than once. Compare it with =COUNTIF($A$2:$A$10,A2)>1: rows where COUNTIF says TRUE but this formula says FALSE are codes that clash only by case, which are worth checking by hand.

Case-sensitive search inside text: FIND vs SEARCH#

EXACT compares whole values. To check whether a cell contains some text with the right capitals, use FIND, which is case-sensitive. SEARCH is the version that ignores case.

=ISNUMBER(FIND("AB",A2))

This returns TRUE if A2 contains a capital "AB" anywhere. With SEARCH it would also match "ab". Our guide to FIND and SEARCH explains the differences, including wildcards. SUBSTITUTE is case-sensitive too, which matters when you swap text with SUBSTITUTE and REPLACE.

Common mistakes with EXACT#

These are the problems you're most likely to hit:

• Hidden spaces. EXACT says FALSE although the values look the same. Check with =LEN(A2) and clean with TRIM.
• Numbers and dates. EXACT compares numbers as text, so a date compared with a typed date string usually fails. Compare dates with = instead.
• Using EXACT with a range on its own. =EXACT(A2:A10,"AB-1") returns a list. In Microsoft 365 it spills a column of TRUE/FALSE; wrap it in SUMPRODUCT, OR or XLOOKUP to get a single answer.
• Expecting COUNTIF to be case-sensitive. It never is. Use the SUMPRODUCT pattern.

Practise it#

Try the live exercise above. Then practise case-sensitive and case-insensitive searching in the FIND and SEARCH lesson, and see how Excel's comparisons treat capitals in the TRUE, FALSE and comparisons lesson.