How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Compute Expert

How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps





In this tutorial, you will learn how to use the EXACT function in excel completely.

When working in excel, we sometimes need to compare the text we have and produce a result based on that. We can use EXACT to help us do the comparison process and get an accurate result from it.

Want to know more about this formula and how to utilize it optimally in excel? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







What is the EXACT Function in Excel?

EXACT function in excel is a function that can help us to compare two texts in a case-sensitive manner.



EXACT Usabilities

You can use EXACT to get the comparison result of two texts in excel. The application of EXACT will become something like a logic condition in excel. Because of this, we use EXACT mainly with another formula that works with logic conditions, like IF.



EXACT Result

The result EXACT gives us is a TRUE or FALSE logic value. If the two texts we compare with EXACT are similar, then EXACT will give us TRUE. If they aren’t the same, then it will give us FALSE.



Excel Version from Which We Can Start Using EXACT

We can start using EXACT in excel since excel 2003.



The Way to Write It and Its Inputs

Here is the general writing form of EXACT in excel.

= EXACT ( text1 , text2 )


When writing EXACT, we just need to input the two texts we want to compare with the formula.

Just so you know, we can input other types of data besides text to EXACT. However, we usually use it to compare text since it is case-sensitive in its comparison process. This case-sensitive nature is something we cannot get if we just compare our data using an equal sign ( = ).

Learn Excel Dashboard Course



Example of Its Usage and Result

Here is the implementation example of the EXACT formula in excel.

How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of the EXACT Implementation Example

As you can see here, EXACT compares the two texts we input into it in a case-sensitive manner. It only gives us TRUE if the two texts are similar in content and letter cases. If the two texts are different in letter cases, even though the content is the same, EXACT will give us FALSE.

EXACT can also compare data that aren’t text. As you can see in the last row of the example, it returns TRUE when it compares similar numbers.



Writing Steps

After we discussed the EXACT writing form, inputs, and implementation example, now let’s discuss its writing steps. You should be able to understand these steps quite easily since EXACT only needs two simple inputs in its writing.

  1. Type an equal sign ( = ) in the cell where you want to put the EXACT result

    How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of Step 1

  2. Type EXACT (can be with large and small letters) and an open bracket sign after =

    How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of Step 2

  3. Input the first text you want to compare. Then, type a comma sign ( , )

    How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of Step 3

  4. Input the second text you want to compare

    How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of Step 4

  5. Type a close bracket sign

    How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of Step 5

  6. Type Enter
  7. Done!

    How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of Step 7




Count Data in a Cell Range in a Case-Sensitive Manner: SUMPRODUCT EXACT

One of the most useful applications of EXACT is when we combine its writing with SUMPRODUCT. We can use this SUMPRODUCT and EXACT combination to count data in a cell range in a case-sensitive manner.

When we have criteria to count data in excel, we usually use COUNTIF or COUNTIFS. However, these two formulas don’t compare the data they count in a case-sensitive manner. Because of this, they will think something like “Apple”, “apple”, or “APPLE” as similar.

Learn Excel Dashboard Course


To count data with a particular criterion in a case-sensitive manner, we should combine SUMPRODUCT and EXACT instead. Here is the general writing form of these two formulas combination to do that counting process.

= SUMPRODUCT ( — ( EXACT ( criterion , data_range ) )


In this formula writing, we write EXACT inside SUMPRODUCT in brackets with a double minus symbol before the bracket. We input the criterion we want for the counting process and the cell range where we count our data inside EXACT.

Want to learn the process that runs after we combine SUMPRODUCT and EXACT like this? First, let’s see the implementation example of the SUMPRODUCT and EXACT combination in excel.

How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of the SUMPRODUCT EXACT Implementation Example

In this example, you can see that we successfully count our data in a case-sensitive manner by using SUMPRODUCT EXACT. We get 3 as the result of SUMPRODUCT EXACT here. That is because there are only 3 data with the same content and letter cases as “ABCDE” in the “Data” column.

How does the SUMPRODUCT EXACT formula writing work so we can get this result? First, because we input data and cell range to EXACT, we will get multiple TRUE and FALSE in an array form. These TRUE and FALSE are results of comparison between the criterion we input (“ABCDE”) with each data in our cell range.

As EXACT is case-sensitive when comparing data, we only get TRUE from data with the same content and letter cases. Thus, this is the TRUE and FALSE array form we get from the EXACT in the example.

{ TRUE , FALSE , TRUE , FALSE , FALSE , FALSE , FALSE , TRUE , FALSE , FALSE }


Notice that we get ten TRUE and FALSE there, the same number as the data amount in the “Data” column. Notice also that the TRUE position corresponds with data similar to “ABCDE”. Meanwhile, the FALSE position corresponds with data not similar to “ABCDE”.

In value, TRUE is the same as 1 and FALSE is the same as 0 in excel. Because of this, we can change the TRUE and FALSE array to a 1 and 0 array using a double minus. That is why we type the symbol in front of the EXACT brackets.

With the help of the double minus, we turn our TRUE and FALSE array into something like this.

{ 1 , 0 , 1 , 0 , 0 , 0 , 0 , 1 , 0 , 0 }


Then, we sum the ones and zeroes in the array with SUMPRODUCT. Usually, we need an array formula form to process an array in excel. But since SUMPRODUCT can process an array too, we don’t need that.

With the sum of the 1 and 0 numbers in the array, we get 3 as our SUMPRODUCT EXACT result. That is the same as the number of “ABCDE” we have in the “Data” column!



Exercise

After you have learned how to use EXACT in excel, now let’s deepen your understanding by doing the following exercise!

Download the exercise file and answer the questions. Download the answer key file if you have done the exercise and want to check your answers. Or probably if you are stuck when trying to get the answers for this exercise!

Link to the exercise file:
Download here

Questions

Use EXACT to answer all these questions below in the exercise file!
  1. What is the logic value you get from the comparison between the two texts in row no. 1?
  2. What is the logic value you get from the comparison between the two texts in row no. 2?
  3. What is the logic value you get from the comparison between the two texts in row no. 3?

Link to the answer key file:
Download here



Additional Note

If you don’t have to be case-sensitive when comparing your data, you can just use an equal sign. Place the sign between the two data you want to compare.

How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of the Formula Writing Example of Data Comparison Using an Equal Symbol

How to Use the EXACT Function in Excel: Usabilities, Examples, and Its Writing Steps - Screenshot of the Result Example of Data Comparison Using an Equal Symbol



Related tutorials you should learn from:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Excel Calculation

Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division in Excel

Average in Excel



Excel Formula

VLOOKUP Function in Excel

IF Function in Excel

SUM Function in Excel

COUNTIFS Function in Excel

SUMIFS Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text in Excel

Excel Worksheet Definition

Excel Range Definition

Excel Shortcuts



Excel Products & Services Recommendation

Best Laptops for Excel

Best Tablets for Excel

Best Keyboards for Excel

Best Mouse for Excel

Best Monitors for Excel



Excel Consultation

About Us

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2022 Compute Expert