EXACT

In this comprehensive guide, we will explore the EXACT function in Microsoft Excel. The EXACT function is a useful tool for comparing two text strings or values to determine if they are identical. This function is case-sensitive, meaning that it considers both the content and the case (upper or lower) of the characters in the strings. The EXACT function can be used in various scenarios, such as data validation, error checking, and ensuring consistency in data entry.

EXACT Syntax

The syntax for the EXACT function in Excel is as follows:

EXACT(text1, text2)

Where:

• text1 is the first text string or value that you want to compare.
• text2 is the second text string or value that you want to compare.

The EXACT function will return TRUE if the two text strings or values are identical, and FALSE if they are not.

EXACT Examples

Let’s look at some examples of how to use the EXACT function in Excel:

1. Example 1: Comparing two text strings

Suppose you want to compare the text strings “Excel” and “excel”. You can use the EXACT function as follows:

=EXACT(“Excel”, “excel”)

Since the EXACT function is case-sensitive, it will return FALSE because the two text strings are not identical in terms of case.

1. Example 2: Comparing two cell values

Assume you have the following values in cells A1 and B1:

• A1: “Data Analysis”
• B1: “Data analysis”

To compare the values in these cells, you can use the EXACT function like this:

=EXACT(A1, B1)

The function will return FALSE because the values in A1 and B1 are not identical in terms of case.

1. Example 3: Comparing numbers

You can also use the EXACT function to compare numeric values. For example, if you want to compare the numbers 123 and 123.00, you can use the following formula:

=EXACT(123, 123.00)

In this case, the function will return TRUE because the two numeric values are identical.

EXACT Tips & Tricks

Here are some tips and tricks to help you get the most out of the EXACT function in Excel:

• Remember that the EXACT function is case-sensitive. If you want to compare text strings without considering case, you can use the UPPER, LOWER, or PROPER functions to convert the text strings to a consistent case before using the EXACT function.
• If you need to compare a large range of cells, you can use the EXACT function in combination with other functions like SUMPRODUCT or COUNTIF to count the number of identical pairs or calculate the percentage of matches.
• When comparing dates, ensure that both dates are in the same format. Excel stores dates as serial numbers, so the EXACT function will return TRUE only if the serial numbers are identical.

Common Mistakes When Using EXACT

Here are some common mistakes that users make when using the EXACT function in Excel:

• Forgetting that the EXACT function is case-sensitive. If you want to compare text strings without considering case, use the UPPER, LOWER, or PROPER functions to convert the text strings to a consistent case before using the EXACT function.
• Comparing dates without ensuring that both dates are in the same format. Excel stores dates as serial numbers, so the EXACT function will return TRUE only if the serial numbers are identical.
• Using the EXACT function to compare numeric values with different formatting, such as currency or percentage. The EXACT function will return FALSE if the formatting is different, even if the underlying values are the same. To compare numeric values without considering formatting, use a different function like ROUND or TRUNC.

Why Isn’t My EXACT Function Working?

If your EXACT function is not working as expected, consider the following troubleshooting tips:

• Check for extra spaces or hidden characters in the text strings. You can use the TRIM function to remove extra spaces before using the EXACT function.
• Ensure that both text strings or values are in the same case. The EXACT function is case-sensitive, so it will return FALSE if the case is different.
• Make sure that both dates are in the same format if you are comparing dates. Excel stores dates as serial numbers, so the EXACT function will return TRUE only if the serial numbers are identical.
• Verify that you are using the correct syntax for the EXACT function. The syntax should be =EXACT(text1, text2).

EXACT: Related Formulae

Here are some related formulae that you might find useful when working with the EXACT function in Excel:

1. UPPER: Converts all characters in a text string to uppercase. Syntax: =UPPER(text)
2. LOWER: Converts all characters in a text string to lowercase. Syntax: =LOWER(text)
3. PROPER: Converts the first letter of each word in a text string to uppercase and the remaining characters to lowercase. Syntax: =PROPER(text)
4. TRIM: Removes extra spaces from a text string. Syntax: =TRIM(text)
5. LEN: Returns the number of characters in a text string. Syntax: =LEN(text)

By combining the EXACT function with these related formulae, you can create more powerful and flexible comparisons in Excel.