When cleaning up large datasets in Microsoft Excel, you will frequently need to compare two lists to find matching values. Most users rely on standard logical formulas, such as typing =A2=B2, to check if the contents of two cells are identical. While this method works for numbers and basic text, it has a significant blind spot: standard Excel formulas are not case-sensitive.
If cell A2 contains “Apple” and cell B2 contains “apple”, the standard equals formula will return TRUE. In many professional scenarios—such as verifying user passwords, checking specific product SKU codes, or cleaning up data imported from a case-sensitive Linux server—this behaviour is unacceptable. To force Excel to acknowledge capitalisation, you must use the EXACT function.
What is the EXACT Function?
The EXACT function does exactly what its name implies. It compares two strings of text and returns TRUE only if they are identical in every single way, including exact capitalisation and spacing. If there is even a single uppercase letter where a lowercase letter should be, or an accidental trailing space, the function will immediately return FALSE.
How to Use the EXACT Function
The syntax for the EXACT function is incredibly straightforward. It requires only two arguments: the two text strings (or cell references) you want to compare.
=EXACT(text1, text2)
Example: Comparing Case-Sensitive SKU Codes
Imagine you have a list of old product codes in Column A (e.g., ITEM-123a) and a list of new product codes in Column B (e.g., ITEM-123A). You need to flag which codes have changed.
- Click on the first empty cell in Column C (for example, C2).
- Type the formula:
=EXACT(A2, B2) - Press Enter.
In this example, the formula will return FALSE, correctly identifying that the lowercase ‘a’ does not perfectly match the uppercase ‘A’. If you had used the standard =A2=B2 formula, Excel would have incorrectly returned TRUE.
Combining EXACT with Other Functions
While EXACT is useful on its own, its true power is unleashed when you nest it inside other logical functions, specifically the IF statement. Returning a simple TRUE or FALSE is not always helpful for generating reports; you often need the spreadsheet to take a specific action based on the result.
Using EXACT with the IF Function
You can instruct Excel to output custom text instead of the default TRUE/FALSE logic.
=IF(EXACT(A2, B2), "Perfect Match", "Error: Case Mismatch")
With this formula, if the two cells are completely identical, your column will display “Perfect Match”. If there is any discrepancy in spelling or capitalisation, it clearly flags the error for review.
Common Pitfalls and Troubleshooting
If the EXACT function is returning FALSE when two cells look visibly identical, the issue is almost always hidden whitespace. Excel treats a space as a valid character.
If cell A2 contains “Hello” and cell B2 contains “Hello ” (with a trailing space at the end), EXACT will return FALSE. To solve this, you can combine EXACT with the TRIM function, which automatically removes invisible spaces before the comparison happens:
=EXACT(TRIM(A2), TRIM(B2))