How to Use the EXACT Function in Excel

The EXACT function in Excel is a text function that compares two text strings and determines whether they are identical. It is case-sensitive, meaning it distinguishes between uppercase and lowercase letters. This makes it particularly useful for tasks where precision in text comparison is required.

What is the EXACT Function?

The EXACT function checks if two text strings are exactly the same, including their case. If the strings match exactly, the function returns TRUE. If there is any difference (even in case), it returns FALSE.

Syntax of the EXACT Function:

=EXACT(text1, text2)

• text1: The first text string or cell reference.
• text2: The second text string or cell reference to compare with the first.

Where Can We Use the EXACT Function?

The EXACT function is helpful in scenarios such as:

1. Validating Data Entry: Ensure that user-entered data matches expected values exactly, including case.
2. Case-Sensitive Comparisons: Compare names, passwords, or codes where case matters (e.g., “John” vs. “john”).
3. Error Checking: Identify discrepancies in text data imported from external sources.
4. Conditional Formatting: Highlight cells where text does not match exactly.
5. Data Cleaning: Verify consistency in text fields like IDs, product names, or email addresses.

How to Use the EXACT Function in Different Methods

There are several ways to use the EXACT function in Excel. Below are four common methods:

Method 1: Comparing Two Text Strings Directly

This method compares two text strings directly within the formula.

Steps:

1. Open Excel and enter your data into a worksheet. For example:

   A1: "Apple"
   B1: "apple"

2. Click on the cell where you want the result (e.g., C1).
3. Type the formula:

   =EXACT(A1, B1)

4. Press Enter. The result (FALSE) will appear in cell C1 because the case does not match.

Live Example:

Cell	Value	Formula	Result
A1	“Apple”
B1	“apple”
C1		=EXACT(A1, B1)	FALSE

Method 2: Comparing Case-Sensitive Names

This method compares names or other text fields where case sensitivity is important.

Steps:

1. Enter your data into a worksheet. For example:

   A1: "John Doe"
   B1: "john doe"

2. In the target cell (e.g., C1), type the formula:

   =EXACT(A1, B1)

3. Press Enter. The result (FALSE) will appear because the case does not match.

Live Example:

Cell	Value	Formula	Result
A1	“John Doe”
B1	“john doe”
C1		=EXACT(A1, B1)	FALSE

Method 3: Using EXACT with Conditional Formatting

You can use the EXACT function to highlight cells where text does not match exactly.

Steps:

1. Enter your data into a worksheet. For example:

   A1: "ProductA"
   B1: "producta"

2. Select the range of cells you want to evaluate (e.g., A1:B1).
3. Go to the Home tab and click Conditional Formatting > New Rule.
4. Choose “Use a formula to determine which cells to format.”
5. Enter the formula:

   =NOT(EXACT(A1, B1))

6. Set the formatting style (e.g., fill color) and click OK.
7. Cells where the text does not match exactly will be highlighted.

Live Example:

Column A	Column B	Conditional Formatting Rule	Highlighted?
“ProductA”	“producta”	=NOT(EXACT(A1, B1))	Yes
“Test”	“Test”	=NOT(EXACT(A2, B2))	No

Method 4: Combining EXACT with IF for Custom Results

You can combine the EXACT function with the IF function to return custom messages based on the comparison.

Steps:

1. Enter your data into a worksheet. For example:

   A1: "Password123"
   B1: "password123"

2. In the target cell (e.g., C1), type the formula:

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

3. Press Enter. The result ("No Match") will appear because the case does not match.

Live Example:

Cell	Value	Formula	Result
A1	“Password123”
B1	“password123”
C1		=IF(EXACT(A1, B1), “Match”, “No Match”)	“No Match”

Live Example Showing Progress in Each Step

Let’s work through a complete example step-by-step:

Data Setup:

Column A	Column B
“Excel”	“excel”
“Data”	“Data”
“Formula”	“formula”

Goal:

Compare the text in Column A with the text in Column B using the EXACT function.

Step 1: Enter the Data

Fill in the values in Columns A and B.

Step 2: Use the EXACT Function

Click on cell C1 and type:

=EXACT(A1, B1)

Step 3: Drag the Formula Down

Drag the formula down to apply it to the entire column. The results will appear in Column C.

Final Table:

Column A	Column B	Column C	Formula
“Excel”	“excel”	FALSE	=EXACT(A1, B1)
“Data”	“Data”	TRUE	=EXACT(A2, B2)
“Formula”	“formula”	FALSE	=EXACT(A3, B3)

Key Notes About the EXACT Function

1. Case-Sensitive: The EXACT function distinguishes between uppercase and lowercase letters.
2. Ignores Formatting Differences: It only compares the actual text content, not font styles, colors, or other formatting.
3. Returns Boolean Values: The result is always either TRUE (exact match) or FALSE (no match).

Conclusion

The EXACT function is a simple yet powerful tool in Excel for performing case-sensitive comparisons of text strings. Whether you’re validating data entry, checking for errors, or applying conditional formatting, the EXACT function ensures precision in your analysis.

By mastering these methods, you’ll be able to handle a wide variety of tasks, from basic text comparisons to more advanced data validation. Practice using the EXACT function with real-world datasets to become proficient!