EXACT Function in Excel: Explained
In this article, you will learn about the Exact formula and its uses in Excel.
What is the EXACT formula in Excel?
The EXACT function is a function in Microsoft Excel that compares two text strings and returns TRUE if they are exactly the same, including their case, and returns FALSE otherwise. The function is case-sensitive, meaning that it distinguishes between uppercase and lowercase letters in text strings.
Syntax of the EXACT function in Excel
The syntax of the Excel EXACT function is as follows:
where text1 and text2 are the two text strings that you want to compare.
Examples: The function would return FALSE if you compare the text strings "apple" and "Apple" since the function is case-sensitive.
Note: The EXACT formula doesn’t care about the formatting differences between the two text strings compared. Another characteristic of the EXACT function is that it considers any leading or trailing spaces in the text strings when comparing them. This means that if you have extra spaces in one of the text strings, even if the rest of the text strings are the same, the function will return FALSE. In such cases, you may need to remove any extra spaces before comparing the text strings.
How to use the EXACT formula in Excel?
Understand the EXACT function with the help of an example case. The below dataset is of country names fetched from two different sources. Now the objective is to find out whether the country names in a dataset exactly match with ones from the other data source or not.
Mapping of the dataset with the syntax of exact function:
where text1 shall represent the country name 1 cell value and text 2 shall represent country name 2 cell value.
Hence our complete function would look like:
Now we need to apply this function in the corresponding cell C2 and press enter to generate the function result.
Now you can simply drag the function to all the cells below the first cell where the function needs to be applied, and the exact match needs to be fetched.
The function is instantly applied to all the cells and the results are displayed in the column. The EXACT function compares both the cell values and returns “TRUE” or “FALSE” depending on the exact matching of the values in the cell. Note, the function is case-sensitive however ignores formatting differences.
As you can see, all the country names are compared for the exact match, and the outcomes are shown in the corresponding column. It is clearly indicative from the example cases that the EXACT function is case sensitive for example: “India” is not the exact match of “INDIA”, or “Australia” doesn’t perfectly match “australia”.
When you look at “United Kingdom” in both columns A and B, they appear to be exactly the same. However, the result is “FALSE” because of the extra space between the word “United” and “Kingdom” in Country Name 2 column. This indicates that the EXACT function even considers a space between words a difference.
When should I use the EXACT Function in Excel?
You should use the EXACT function in Excel when you need to compare two text strings and ensure that they are exactly the same, including their case. Here are some specific scenarios where the EXACT function can be useful:
- Data validation: If you have a data entry form or worksheet where users are expected to enter specific text values, you can use the EXACT function to validate their input and make sure that they have entered the correct values.
- Data cleaning: If you have a large dataset with inconsistent text values (e.g., "New York", "new york", "NewYork", etc.), you can use the EXACT function to identify and clean up any duplicates or inconsistencies.
- Filtering and sorting: If you need to filter or sort a table based on specific text values, you can use the EXACT function in combination with other Excel functions like FILTER, SORT, and IF to create custom filters and sorting criteria.
What are the differences between the EXACT function and other text-related formulas in Excel?
The main difference between the EXACT function and other text functions in Excel is that the EXACT function is case-sensitive, meaning that it distinguishes between uppercase and lowercase letters Other text functions, such as the = operator or the IF function, are not case-sensitive and may ignore leading or trailing spaces. Another difference between the EXACT function and other text functions in Excel is that the EXACT function is a logical function that returns TRUE or FALSE depending on whether the two text strings are exactly the same. Other text functions may return different types of values, such as numbers or text strings, depending on their specific use case.
