What is the EXACT Function?
The EXACT Function is categorized under TEXT functions. The function helps to test if two or more than two supplied text strings or values are exactly equal and if so, returns TRUE. Otherwise, it returns FALSE. The EXACT function is case-sensitive.
In financial analysis, we often need to compare two values while analyzing data and the EXACT function helps us to do the comparison. Also, it can be useful when we share our worksheet with others and want them to input data in a specific format.
=EXACT(text1, text2, …)
- Text1 (required argument) – It is the first string which we wish to join. It can be two text strings, numbers or logical value.
- Text2 (required argument) – It is the second text string.
The EXACT function returns TRUE if the two strings are the same. The EXACT function returns FALSE if the two strings are different.
How to use the EXACT Function in Excel?
To understand the uses of this function, let’s consider a few examples:
Let’s take few examples to understand how the EXACT function works. Suppose we wish to compare following data:
The formula used and the result we get is as follows:
Let’s analyze the data:
- In the first example, the result is true as the strings match.
- As the text strings in the next row are different, one is in upper case and the second is in lower case, we get the result as false. As the function is case sensitive, we get the result as FALSE.
- Similarly, we get false for next two examples as they don’t match.
Suppose we wish that users of our worksheet enter data in PROPER case. Only then we can use data validation with a custom formula based on the EXACT and other functions.
Suppose the data given is as follows:
We wish any user of this worksheet to input remarks in proper case only. We can then click on data validation under Data tab and select Data Validation. Change the validation criteria to custom and input the formula =AND(EXACT(C5,PROPER(C5)),ISTEXT(C5)).
Now if any user tries to put any value that is not in the proper case, we will get the following error:
How the formula worked:
The Data validation rules we set are triggered when a user adds or changes a cell value. The PROPER function changes text values to proper case, and the EXACT function performs a case-sensitive comparison. Whereas, the AND function takes multiple arguments (logical conditions) and returns TRUE only when all arguments return TRUE.
So the first logical condition would compare the value input by the user to a proper case version of the same value EXACT(C3,PROPER(C3) and the second logical condition tests that input to C5 is actually text ISTEXT(C3).
Now if both the above conditions are true then the AND function returns TRUE and the input passes validation. If either condition is FALSE, AND returns FALSE and input fails the data validation.
The EXACT function is quite useful when we wish to deal with case-sensitive data.
Using the different product details and their revenue for the year:
As shown above, there are two products soft toys one being in lowercase and other in uppercase. Excel functions such as VLOOKUP and INDEX/MATCH are not case-sensitive. As we are looking out for numeric values, SUMPRODUCT + EXACT is an exciting and flexible way to do a case-sensitive lookup.
The formula to be used is =SUMPRODUCT(–(EXACT(E3,B3:B8)),C3:C8).
The above formula will run a test on the values in column B, then convert the resulting TRUE/FALSE values to 1’s and 0’s. The reason we use double negative is that it converts the resulting TRUE & FALSE values to 1’s and 0’s.
We will get the following result:
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: