How to Use Excel EXACT Function (6 Suitable Examples)

The Excel EXACT function tests two text strings and returns TRUE or FALSE depending on their exact match or otherwise respectively. The EXACT function is case-sensitive, which means it takes upper and lower cases into account.

 In this article, you’ll get to learn how you can use this EXACT function with suitable examples.

Excel EXACT Function: Syntax and Arguments

⦽ Function Objective:

The EXACT function compares two texts then returns TRUE (in case the texts are exactly the same) or FALSE (in case the texts are exactly not the same).

⦽ Syntax:

 EXACT (text1,text2)

⦽ Arguments Explanation: 

⦽ Return Parameter:

 TRUE or FALSE, depending on the exact match between two arguments.

⦽ Version:

Excel 2019

How to Use the Excel EXACT Function: 6 Suitable Examples

Example 1: Using Excel EXACT Function Compare Entries in Dataset 

We have two text strings in columns B and C. We want to check if the entries in columns B and C are an exact match or not. The result will be in TRUE or FALSE appearing in column E.

⧫ With proper cell references, enter the following formula in any cell (i.e., D5).

⧫ Press ENTER.

After applying the formula to other cells, you can simply find out the difference between Text 1 and Text 2 in data types as shown in the below picture.

Here, the exact matches among entries in columns B and C result in TRUE otherwise FALSE.

Example 2: Checking Text If in Upper or Lower Case Using EXACT  

Upper Case:

If you want to see whether a text string is in all caps or not, you can use the EXACT function combined with the UPPER function. The UPPER function transforms any text into all caps.

⧫ The required formula to do so is

Write it in any blank cell (i.e., C5) with desired cell references.

⧫ Press ENTER. It brings up the result.

You can use the formula for other entries, it returns TRUE or FALSE depending on the data types.

Lower Case:

⧫ Similar to uppercase, we can also check texts in any cell whether it is in lowercase or not using the LOWER function. The required formula is

Paste the formula in any desired cell (i.e., C5).

⧫ Press ENTER then TRUE or FALSE text will appear depending on the criteria.

The EXACT function entwined with the LOWER function returns TRUE for all lowercase entries and FALSE for otherwise.

Example 3: Entering Data in Specific Format Using Excel EXACT Function

In this case, you want to enter entries maintaining a specific format (i.e., Proper). You want all the text entered in any cell to be in Proper format. To achieve this, you can use Excel’s Data Validation feature. By using the Data Validation feature, you can apply a custom formula made of the EXACT function to maintain the desired format.

⧫ First, Check the following custom formula by applying it in any blank cell (i.e., C5) if it works or not.

⧫ Press ENTER and you’ll see the custom formula returns TRUE as text in B5 is in Proper format.

You can use the formula for other entries to see their status.

⧫ To apply Data Validation, Select a range of cells then go to Data tab > Select Data Validation (from Data Tools section).

Data Validation window appears. In the Data Validation dialog box,

⧪ Select Custom in the Allow command box.

⧪ Enter the custom formula in the Formula command box.

You can also set an Input (i.e., Input Type; Please Enter Text in Proper Format) and Error Message (i.e., Input Error; Please Enter Text in Proper format) to guide the users.

⧪ Click OK.

⧫ Now, back to the worksheet and try to enter non-proper text (i.e., Jane doe), you’ll be barred and an Error Message will come up (if you set it earlier).

The below picture shows all the entry types and you can restrict them by applying Data Validation in any range of cells.

Example 4: Using EXACT Function in Case Sensitive Formulas

The EXACT function is a case-sensitive function. Upper cases and lower cases in texts are distinguished as different entries.

We have case sensitive IDs and we want to count any occurrence for a specific ID (i.e., FCL002)

⧫ First, use the following formula to count the occurrence of FCL002 in the dataset.

You get 3 as the COUNTIF function is not case-sensitive.

⧫ On the other hand, using the SUMPRODUCT function reinforced with the EXACT function makes it a case-sensitive function. Now, use the following formula to count the same ID (i.e., FCL002) and you get 1.

Example 5: Spot Extra Space Using EXACT

While working in Excel, users enter spaces (leading, trailing, or inside) in cells along with the entries. With bare eyes it’s hard to distinguish the spaces, you can use the EXACT function to compare between two texts and return with TRUE or FALSE.

⧫ Paste the following formula in any cell (i.e., D5).

⧫ Press ENTER. Though the two texts (i.e., B5, C5) seem pretty similar to bare eyes, you can find a leading space in one entry. Thus, the EXACT function returns FALSE.

You can evaluate all the entries for any kind of space using the EXACT function.

Read More: [Fixed!] Excel EXACT Function Not Working 

Example 6: Ignoring Cell Formats

In Excel, we normally use multiple cell formats to declare the data types. Currency, Text, Phone Number, Social Security Number, etc. are the typical cell formats we use. The EXACT function ignores these types of cell formats and counts them as the same.

⧫ Paste the following formula in any cell (i.e., D5)

⧫ Press ENTER and drag the Fill Handle to apply the formula to the other entries. You see, except Date format, the EXACT function considers all the entries as the same.

So, the EXACT function ignores formats except for the Date format as shown in the following picture.

⧭ Things to Keep in Mind

🔼 The EXACT function is case-sensitive and converts numeric values to texts.

🔼 It returns TRUE or FALSE depending on case patterns of texts.

🔼 Also the function ignores cell formats (except some cell formats like Date, Time).

Download Excel Workbook

Conclusion

I hope the above-described uses of the EXACT function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. You can check out my other articles on the Exceldemy website.

<< Go Back to Excel Functions | Learn Excel