EXACT Function Examples – Excel & Google Sheets
Download the example workbook
This Tutorial demonstrates how to use the Excel EXACT Function in Excel to test if cells are exactly the same.
EXACT Function Overview
The EXACT Function Test if two text cells are exactly equal. Returns TRUE or FALSE. Case-sensitive.
To use the EXACT Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
EXACT Function Syntax and Inputs:
=EXACT(text1,text2)
text1 – A string of text.
text2 – A string of text.
How to use the EXACT Function in Excel:
The EXACT function checks if two text strings are well… exactly the same. This includes the case (upper or lower) of all letters. It returns a TRUE if they are the same, and FALSE if they are not.
=EXACT(B3,C3)
As seen above, when comparing “apple”, TRUE is only returned when all cases match.
Numbers in row 6 and dates (which are stored as numbers) in row 7 won’t have issues with case sensitivity. They just have to be the same number or date to return TRUE.
Check If Text in Upper Case
You might require users of a file to type an ID in upper-case. Use this formula to check that they’ve correctly entered text in upper-case:
=EXACT(B3,UPPER(B3))
UPPER converts all characters in ID to upper casing. Using that, you can compare if the original ID typed in B3:B7 to the ID with all upper casing. This formula can also be placed in a Data Validation to stop them from entering in the first place or in Conditional Formatting to highlight that it’s wrong.
Case-Sensitive COUNTIF
COUNTIF counts the number of cells that meet a criteria. It is NOT case-sensitive.
=COUNTIF(B3:B8,D3)
Notice in the above example COUNTIF returns 3, which includes the lower case “mlf9377”.
To perform a case-sensitive COUNTIF, use the SUMPRODUCT and EXACT Functions instead:
=SUMPRODUCT(--(EXACT(B3:B8,D3)))
Read our article, to learn more about advanced uses of the SUMPRODUCT Function.
EXACT Function in Google Sheets
The EXACT Function works exactly the same in Google Sheets as in Excel:
Additional Notes
The EXACT Function is case-sensitive. To ignore case, use LOWER (or UPPER) to put the strings of text in the same case. <>
Erroneous spaces at the beginning or end of the text string will result in a value of FALSE. Use the TRIM Function to remove erroneous spaces <>
The EXACT Function ignores cell formatting. ex. 0.08 = 8%