EXACT Function in Excel
Excel is very easy to use and has simple functions with two arguments. The EXACT function is an inbuilt function in Excel that is categorized under the TEXT function. It is used as a worksheet & VBA function in Excel. EXACT function in Excel is used as a part of a formula in a cell of a worksheet & also integrated with other Excel functions (IF, SUMPRODUCT & various other formulas of Excel). Most people use it to perform a case-sensitive lookup between two strings.
Definition
Checks whether two strings are the same and return TRUE or FALSE. It means the EXACT function in Excel compares two strings between two cells and returns TRUE if both values are the same & similar. Otherwise, it will return FALSE if both the strings do not match.
EXACT Formula in Excel
Below is the EXACT Formula in Excel:
Explanation of the EXACT Function in Excel
The EXACT formula in Excel has two parameters, i.e., text1 and text2.
- Text 1: We want to compare the first text string.
- Text 2: It is the second string that we want to compare.
The arguments can be entered manually, or cell references can be given. The exact function is case-sensitive. It will return a FALSE value if there is a Leading or trailing space in any text strings, small or capital letters in a text string, Extra space between alphabets in a text, or uppercase or lowercase text between two strings or text, i.e., Capital letters are not equal to small letters.
The equal sign in a formula of exact function can also be used, i.e. (text1=text2), to compare text strings. Instead of the EXACT function, the double equals (==) comparison operator is also used to compare two strings. For example, =X1==Y1 returns the same value as =EXACT(X1,Y1).
How to Use EXACT Function in Excel?
The EXACT function in Excel is very simple and easy to use. Let’s understand the working of the EXACT function in Excel by some EXACT Formula examples.
Example #1 – Text Containing Uppercase Letters
Here is the below-mentioned example. Cell B9 & C9 contain text strings; with the help of the EXACT function, I need to check whether the two cells have the same data.
- Let’s apply the exact function in cell “D9”. Select the cell “D9,” where an EXACT function needs to be applied.
- Click the insert function button (fx) under the formula toolbar, and a dialog box will appear, type the keyword “EXACT” in the search for a function box, and the EXACT function will appear in Select a function box. Double-click on an EXACT function.
- A dialog box appears where arguments for the EXACT function need to be filled or entered, i.e., =EXACT(B9, C9). Click ok once it is entered. Here, I need to compare between B9 & C9 cells; hence it is entered in the function arguments of an exact function.
- An EXACT function returns a FALSE value because both strings do not match each other. Here ‘c’ and ‘C’ are different, i.e., ‘c’ is lowercase in text1, whereas ‘C’ is uppercase in text2 (EXACT function is case-sensitive)
- In the below-mentioned example, the EXACT function returns a TRUE value, Because both values in the cell B10 & C10 are the same & similar. i.e., Both the cells contain a proper case text string.
Example #2 – Text Containing Spaces
Here is the below-mentioned example. Cell B13 & C13 contain the text strings; with the help of the EXACT function, I need to check whether the two cells have the same data.
- Let’s apply the exact function in cell “D13”. Select the cell “D13,” where an EXACT function needs to be applied.
- Click the insert function button (fx) under the formula toolbar, and a dialog box will appear, type the keyword “EXACT” in the search for a function box, and the EXACT function will appear in Select a function box. Double-click on an EXACT function.
- A dialog box appears where arguments for the EXACT function need to be filled or entered, i.e., =EXACT(B13, C13). Click ok once it is entered. Here, I need to compare between B13 & C13 cells; hence it is entered in the function arguments of an exact function.
- An EXACT function returns a FALSE value because both strings do not match each other. ‘XY Z’ and’XYZ’ are different, i.e., ‘ XY Z’ contains space between letters Y & Z in text1, whereas ‘XYZ’ does not have any extra space between letters in the text2 column.
- An EXACT function returns a FALSE value if there is any extra space between alphabets in text or even if there is a Leading or trailing space in any text strings. In the below-mentioned example, the EXACT function returns a FALSE value because there is a leading space in the cell “B14” before the word XYZ; you can observe two extra spaces.
Example #3 – Ignoring Format Pattern
In the below-mentioned example, Cell B18 contains $99, where it is in dollar value as currency format & C18 contains 99, in number or general format. With the help of the EXACT function, I need to check whether the two cells have the same data.
- Let’s apply the exact function in cell “D18”. Select the cell “D18,” where an EXACT function needs to be applied.
- Click the insert function button (fx) under the formula toolbar, and a dialog box will appear, type the keyword “EXACT” in the search for a function box, and the EXACT function will appear in Select a function box. Double-click on an EXACT function.
- A dialog box appears where arguments for the EXACT function need to be filled or entered, i.e., =EXACT(B18, C18). Click ok once it is entered. Here, I need to compare between B18 & C18 cells; hence it is entered in the function arguments of an exact function.
- An EXACT function returns a TRUE value because both cells contain the same value, i.e., 99. Here Exact function ignores the format pattern of cells.
- In the example below, the EXACT function returns the TRUE value because both cells contain the same date value, i.e., Cell “B19” contains the date value (5/16/18) in number or general format. In contrast, in cell “C19,” the date value is present in date format. Here Exact function ignores the format pattern of cells.
Things to Remember
- The EXACT function is case-sensitive. i.e., Capital letters are not equal to small letters.
- The exact function in Excel returns the false value if there is a difference in case pattern between text strings, i.e., sentence case, lowercase, uppercase, proper case & toggle case.
- The exact function in Excel returns the false value if there is Leading or trailing space & Extra space between alphabets in the text between two text strings.
- EXACT Function in Excel ignores the formatting difference between the two cells.
E.G. 0.08 = 8%
$99 = 99
Recommended Articles
This has been a guide to the EXACT function in Excel. Here we discuss the EXACT Formula in Excel and how to use the EXACT Function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –
