The EXACT formula with example in Excel

The EXACT function in Excel is used to compare two text strings and determine if they are exactly the same, including case sensitivity. If the texts are identical, it returns TRUE; otherwise, it returns FALSE.

Syntax

excel
EXACT(text1, text2)

• text1: The first text string to compare.
• text2: The second text string to compare.

Examples

Let's go through some examples to see how the EXACT function works in different scenarios.

Example 1: Basic Usage

Suppose you have the following text strings in cells A1 and B1:

• A1: Hello
• B1: hello

To check if these strings are exactly the same:

1. In cell C1, enter the formula:

   excel
   =EXACT(A1, B1)
   

   This will output FALSE, because the EXACT function is case-sensitive, and Hello is not the same as hello.

Example 2: Case Sensitivity

Suppose you have the following text strings in cells A2 and B2:

• A2: Excel
• B2: Excel

To check if these strings are exactly the same:

1. In cell C2, enter the formula:

   excel
   =EXACT(A2, B2)
   

   This will output TRUE, because both text strings are identical, including the case.

Example 3: Comparing Numbers as Text

Suppose you have the following numbers formatted as text in cells A3 and B3:

• A3: 123
• B3: 123

To check if these text representations of numbers are exactly the same:

1. In cell C3, enter the formula:

   excel
   =EXACT(A3, B3)
   

   This will output TRUE, because the text strings 123 are identical.

Example 4: Spaces in Text Strings

Suppose you have the following text strings in cells A4 and B4:

• A4: Data
• B4: Data (with a trailing space)

To check if these strings are exactly the same:

1. In cell C4, enter the formula:

   excel
   =EXACT(A4, B4)
   

   This will output FALSE, because the trailing space in B4 makes the two strings different.

Practical Use Cases

1. Data Validation: Ensuring that entries match exactly, such as matching case-sensitive passwords or codes.
2. Quality Control: Verifying that product codes or IDs are entered correctly and consistently.
3. Conditional Formatting: Highlighting cells that do not match exactly for review or correction.

Example: Data Validation

Suppose you have a list of expected product codes in column A and the actual scanned product codes in column B. You want to check if they match exactly:

1. In column C, enter the formula:

   excel
   =EXACT(A5, B5)
   

   Drag the formula down to compare each row.

If cell A5 contains P1234 and cell B5 contains p1234, the formula will return FALSE due to case sensitivity.

Summary

The EXACT function in Excel is a simple yet powerful tool for comparing text strings with case sensitivity. Whether you're validating data, performing quality control, or applying conditional formatting, EXACT helps ensure that your text comparisons are accurate and reliable.