EXACT function

The EXACT function in Excel is used to compare two text strings and check if they are exactly the same, including case sensitivity. It returns TRUE if the two strings are exactly the same and FALSE if they are not.

Syntax

=EXACT(text1, text2)

Parameters

• text1 (required): The first text string or cell reference containing the first text you want to compare.
• text2 (required): The second text string or cell reference containing the text you want to compare with the first text.

Key Points

• Case Sensitivity: The EXACT function is case-sensitive, meaning it will treat uppercase and lowercase letters as different. For example, “apple” and “Apple” are considered different strings.
• Exact Match: The function checks for an exact match between the two text strings, including any spaces, punctuation, or special characters. Even a small difference, like an extra space, will cause it to return FALSE.
• Return Values: The function returns:
  • TRUE if the two text strings are identical.
  • FALSE if the two text strings are different.

Example Usage

1. Basic Comparison (Case-Sensitive)

If cell A1 contains “apple” and cell B1 contains “apple”, the function will return TRUE:

=EXACT(A1, B1)

This will return:

TRUE

If cell B1 contains “Apple” (with an uppercase “A”), the function will return FALSE because the case is different:

=EXACT(A1, B1)

This will return:

FALSE

2. Comparison with Different Text

If cell A1 contains “apple” and cell B1 contains “orange”, the function will return FALSE because the strings are different:

=EXACT(A1, B1)

This will return:

FALSE

3. Including Spaces

The EXACT function also considers spaces. If cell A1 contains “apple” and cell B1 contains “apple ” (with an extra space at the end), the function will return FALSE:

=EXACT(A1, B1)

This will return:

FALSE

4. Using in Conditional Formulas

You can use the EXACT function within other formulas to perform actions based on whether two text strings match. For example, using EXACT with an IF function:

=IF(EXACT(A1, B1), "Match", "No Match")

This will return:

• “Match” if the values in A1 and B1 are exactly the same.
• “No Match” if the values in A1 and B1 are different.

Notes

• Case Sensitivity: If you want to compare text without considering case sensitivity, use the =A1=B1 comparison instead, which is not case-sensitive.
• Whitespace: Be mindful of leading or trailing spaces in your text strings, as EXACT will consider these as differences between the two strings.
• Text Comparisons: The EXACT function is specifically for text comparisons. If you are comparing numbers, Excel does not differentiate between the formats or number types and will treat them as the same if their values are equal.

Related Functions

• LOWER and UPPER: If you want to compare text without worrying about case sensitivity, you can use the LOWER or UPPER function to standardize the text before comparing:

  =EXACT(LOWER(A1), LOWER(B1))
  

  This will return TRUE even if “apple” and “APPLE” are compared because both will be converted to lowercase.

• TRIM: If you suspect that extra spaces might be causing mismatches in your text, you can use the TRIM function to remove extra spaces before comparing:

  =EXACT(TRIM(A1), TRIM(B1))
  

• TEXT: If you want to compare numeric values formatted as text, you can use the TEXT function to ensure both values are formatted the same way.

The EXACT function is useful when you need to compare text values in Excel, especially in cases where the distinction between uppercase and lowercase letters, or the presence of extra spaces, matters. It provides an efficient way to ensure that two strings match exactly as they appear.