Excel EXACT Function: Syntax, Examples and Tips

Q: How do I make COUNTIF or VLOOKUP case-sensitive?

Neither function is case-sensitive. Use SUMPRODUCT(--EXACT(range,value)) to count, and INDEX with MATCH(TRUE,EXACT(range,value),0) to look up.

Part of the free Module 5: Excel Formulas and Functions · Function 28 of 105 · Full Excel course

The Excel EXACT function compares two text strings and returns TRUE when they are identical, including letter case, and FALSE otherwise. Because the standard equals operator ignores case, EXACT is the tool to use when “Apple” and “apple” must be treated as different values, for example when checking passwords, product codes or imported data.

Table of Contents

Syntax

=EXACT(text1, text2)

Arguments

Argument	Required / Optional	Meaning
text1	Required	The first text string or cell reference.
text2	Required	The second text string or cell reference to compare with text1.

How EXACT works

EXACT converts both arguments to text and compares them character by character. Case matters, spaces matter and formatting does not. =EXACT("Excel","excel") returns FALSE, =EXACT("Excel ","Excel") returns FALSE because of the trailing space, and =EXACT(123,"123") returns TRUE because the number 123 is converted to the text “123” before comparison. By contrast, ="Excel"="excel" returns TRUE.

Step-by-step example: verify re-typed codes

Column A holds original product codes such as AB-1001x; column B holds the codes re-entered by a colleague.
In C2 enter =EXACT(A2,B2) and fill down.
Rows showing FALSE contain a difference. Apply a filter on column C to list only the mismatches.
If many mismatches are caused by trailing spaces, change the formula to =EXACT(TRIM(A2),TRIM(B2)) to ignore them.

Practical use cases

1. Case-sensitive data validation

=EXACT(A2,UPPER(A2))

Returns TRUE only when the entry is entirely upper case. Use it as a custom Data Validation rule to force upper-case codes.

2. Case-sensitive COUNTIF replacement

=SUMPRODUCT(--EXACT(A2:A100,"admin"))

COUNTIF is not case-sensitive; this formula counts only the cells that match “admin” exactly.

3. Case-sensitive lookup

=INDEX(C2:C100,MATCH(TRUE,EXACT(A2:A100,E2),0))

In Excel 365 this works as written; in older versions confirm it with Ctrl+Shift+Enter.

4. Flag changed records between two versions of a list

=IF(EXACT(A2&B2&C2,F2&G2&H2),"Same","Changed")

5. Password or code confirmation field

=IF(EXACT(B2,C2),"Match","Codes differ, check capital letters")

Compares an entry with its confirmation and reports a mismatch even when the only difference is case, which the = operator would miss.

Common mistakes and tips

Unexpected FALSE: hidden spaces or non-breaking characters are the usual reason. Wrap both arguments in TRIM or CLEAN.
Comparing numbers: EXACT compares the text form, so 1.0 and 1 are TRUE (both become “1”) but a cell formatted as 1.00 still stores 1. Use the equals operator for numeric comparison.
Dates: both dates are converted to serial numbers as text, so EXACT works, but it is clearer to compare dates with =.
Need case-insensitive comparison? Use =A2=B2; EXACT is only for case-sensitive checks.
EXACT never returns an error for text input; a #VALUE! comes from an error in one of the referenced cells.

EXACT in conditional formatting and data validation

EXACT is not limited to worksheet cells. In Conditional Formatting, the rule =NOT(EXACT(A2,B2)) applied to A2:B100 highlights every pair of cells that differ in any way, including case, which is useful when reconciling two exports of the same list. In Data Validation, choose Custom and enter =EXACT(A2,UPPER(A2)) to reject lower-case input, or =EXACT(A2,PROPER(A2)) to insist on capitalised names. EXACT also helps when Excel treats two values as equal that you consider different: because the = operator and functions such as VLOOKUP, MATCH and COUNTIF are all case-insensitive, EXACT is the only built-in way to distinguish “ID001a” from “ID001A”. Pair it with SUMPRODUCT for counts and with INDEX and MATCH for case-sensitive lookups.