How to Use the Excel EXACT Function: Compare Strings for an Exact Match

スポンサーリンク
スポンサーリンク

About the EXACT Function

Overview of EXACT

Check if Two Strings Are EqualExcel Function / Text Operations

=EXACT( text1 , text2 )

Overview Determines whether two strings are equal (cannot specify more than two arguments or a range)

  • Compares strings and returns TRUE if they are equal and FALSE if they are not
  • Distinguishes between full-width and half-width characters
  • To check if numbers are equal, the DELTA function can also be used. The differences between DELTA and EXACT are outlined below:
    DELTA EXACT

    Returns 1 if equal

    Returns TRUE if equal

  • It is a function to compare two values and cannot compare more than two strings

When to Use EXACT

  • To perform a double-check and ensure there are no errors

How to Use EXACT

  A B C D E
1    
2    
3 ABCDE ABCDE FALSE

=EXACT( B3 , C3 )

※ C3 contains full-width “DE”, so it displays FALSE

 
4    
5
6  
7    

Why Does FALSE Appear Even When Strings Look Identical?

  • If FALSE appears when the strings seem identical, it is possible that the formatting is different. Check this under [Home] → [Number] -> (dropdown menu). For example, the date 2023/4/21 formatted as a date and the same date as text will be treated as different and return FALSE.
  • Other reasons may include trailing spaces in the strings.

Using EXACT with AND to Compare More Than Two Strings

  • EXACT compares only two strings, but you can use the AND function to check for equality across multiple strings.
  • For three strings, AND(EXACT(A3,B3),C3) is incorrect. This treats C3 as always TRUE regardless of its value. Instead, you must use EXACT twice as shown below:
  A B C D E
1    
3 New York New York New York TRUE

=AND(EXACT(A3,B3),EXACT(B3,C3))

Compares A3 and B3; if TRUE, compares B3 and C3. Returns TRUE only if both conditions are met.

 
4    
5

For four strings, add EXACT(C3, D3) within the AND function

6  
7    

Combining EXACT and AND for Four or More Strings

A B C D
EXACT(A, B) EXACT(C, D)
FALSE TRUE TRUE FALSE
-> Does not satisfy v v -> Does not satisfy
EXACT(EXACT(A, B), EXACT(C, D))
TRUE FALSE
Satisfies Does not satisfy

Summary of EXACT

  • The Excel EXACT function is a convenient way to check if two strings are exactly identical. It distinguishes between uppercase and lowercase letters, so even subtle differences will not go unnoticed. However, if spaces or special characters are included, seemingly identical strings might be deemed different, so input data accuracy is crucial.
  • Functions similar to EXACT include the IF function, which can be combined to perform conditional operations. For text processing, functions like TEXT or TRIM can be used alongside EXACT to achieve more accurate results.