EXACT vs = in Excel: Case-Sensitive Comparisons

The short answer to EXACT vs equals in Excel: the equals sign (=) compares values without caring about upper and lower case, while the EXACT function compares text including case. So ="apple"="APPLE" returns TRUE, but =EXACT("apple","APPLE") returns FALSE. This comparison is for anyone checking codes, IDs or names where capital letters matter, and for anyone puzzled by a match that "should" have failed.

EXACT vs = at a glance#

The equals sign is a quick, case-blind comparison; EXACT is a strict, case-aware one.

How the equals sign compares values#

Typing =A2=B2 in a cell compares the two values and returns TRUE or FALSE. It is a comparison operator, like > or <.

=A2=B2
=IF(A2=B2,"Match","Check")

For text, the comparison ignores case: Excel, EXCEL and excel are all equal. It does not ignore spaces, so "Excel " with a trailing space is different. For numbers, it compares values, so 5 equals 5.0. A number never equals text, so the number 1 and the text "1" are not equal. The TRUE and FALSE comparisons lesson practises all the comparison operators.

Every criteria-based function in Excel, including COUNTIF, SUMIF, MATCH, VLOOKUP and XLOOKUP, compares text the same case-blind way.

How EXACT compares text#

EXACT takes two values, converts them to text, and returns TRUE only if every character matches, including case.

=EXACT(A2,B2)
=EXACT("AB-100","ab-100")

The second formula returns FALSE because the letters differ in case. Because EXACT compares text, the number 1 and the text "1" count as the same, which is the opposite of =. Formatting doesn't matter: a bold "Paid" matches a plain "Paid". The guide to case-sensitive matching with EXACT covers more uses.

The same codes checked both ways#

Here are product codes typed by six clerks. The correct code is AB-100.

The equals sign accepts four codes, because it ignores case. EXACT accepts only two. Row 6 fails both tests, because the trailing space is a real character, even though you can't see it.

Counting the matches#

=COUNTIF(A2:A7,"AB-100")
=SUMPRODUCT(--EXACT(A2:A7,"AB-100"))

COUNTIF returns 4: it is case-blind, just like =. The SUMPRODUCT version returns 2. EXACT checks each cell and returns a list of TRUE and FALSE values; the double minus (--) turns them into 1s and 0s; and SUMPRODUCT adds them up.

Looking up a case-sensitive value#

Suppose A2:A6 holds codes such as ab-1 and AB-1 that differ only by case, and B2:B6 holds prices. To return the price for the code in E2, matching case:

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

EXACT builds a TRUE/FALSE list, and the lookup finds the first TRUE. XLOOKUP needs Excel 2021 or Microsoft 365. The INDEX MATCH version works everywhere; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter (⌘+Shift+Return on a Mac). Note that "exact match" in VLOOKUP means "not approximate", not "case-sensitive"; see VLOOKUP exact match.

Comparing two whole lists#

EXACT also works across two columns at once. If A2:A7 holds codes from one system and B2:B7 holds the same codes from another, this counts the rows that match perfectly:

=SUMPRODUCT(--EXACT(A2:A7,B2:B7))

To see which rows differ, select A2:B7, choose Home > Conditional Formatting > New Rule > Use a formula, and enter =NOT(EXACT($A2,$B2)). Every row where the two codes differ in any way, including case and spaces, is highlighted. The dollar signs lock the columns, so the rule checks each row against its own pair.

Other functions that care about case#

EXACT isn't the only case-sensitive function. FIND is case-sensitive, while SEARCH is not: =FIND("ab","AB-100") returns #VALUE!, but =SEARCH("ab","AB-100") returns 1. SUBSTITUTE is case-sensitive too, so =SUBSTITUTE(A2,"ab","XY") leaves "AB" untouched. Knowing which functions respect case helps you avoid surprises when cleaning text.

When to use =#

Use the equals sign for everyday comparisons. It fits:

• Names, categories and statuses, where "north" and "North" should count as the same.
• IF tests, such as =IF(C2="Paid","Done","Chase").
• Comparing numbers and dates, where case doesn't apply.
• Checking two lists match after you've cleaned them, such as =A2=B2 filled down.
• Data validation and conditional formatting rules, such as highlighting rows where a status equals "Overdue", whatever case people type it in.
• Job test questions that ask whether two cells hold the same value. Unless the task mentions case, the equals sign is the expected answer.

When to use EXACT#

Use EXACT when upper and lower case carry meaning. It fits:

• Product codes, SKUs and part numbers where ab-100 and AB-100 are different items.
• Case-sensitive IDs and usernames in exported system data.
• Auditing data entry, to find codes typed in the wrong case before an import.
• Case-sensitive counts and lookups, combined with SUMPRODUCT, MATCH or XLOOKUP.
• Detecting invisible differences, since EXACT and LEN together show when two cells only look the same.
• Reconciling exports from two systems, where one system stores codes in capitals and another doesn't, and you need to know exactly which records were changed.

Common mistakes when comparing text#

1. Expecting = to catch case differences. It never does. Use EXACT.
2. Forgetting about spaces. Both tests fail on a trailing space. Clean first with TRIM; the comparison of TRIM vs CLEAN explains which removes what.
3. Using EXACT inside COUNTIF. COUNTIF doesn't accept a function as its criteria range. Use SUMPRODUCT with EXACT.
4. Comparing numbers stored as text with =. =A2=B2 returns FALSE for 100 and "100". Convert with VALUE, or compare with EXACT if you only care about the characters.
5. Assuming "exact match" means case-sensitive. In VLOOKUP and MATCH, exact match still ignores case.
6. Fixing case by hand. If case doesn't matter, standardise it with UPPER or LOWER instead of checking with EXACT. For other text fixes, see SUBSTITUTE vs REPLACE and the guide to cleaning messy data.

Our recommendation#

Use the equals sign by default. Most comparisons in real work, from statuses to names, should ignore case, and = is short and familiar. Switch to EXACT only when capital letters change the meaning, such as codes, IDs or usernames, and pair it with SUMPRODUCT, MATCH or XLOOKUP for counts and lookups. Whichever you use, run TRIM on imported text first, because a hidden space fails both tests.

In short: = when case doesn't matter, EXACT when it does, and TRIM before either.

Practise it#

Try the exercise on this page: count codes that match AB-100 exactly, case and all. Then practise TRUE and FALSE comparisons in a live spreadsheet and the cleaning text lesson: you type each formula, and it checks your answer instantly.