[Fixed!] Excel EXACT Function Not Working (3 Suitable Solutions)

Get FREE Advanced Excel Exercises with Solutions!

One of the many functions available in Excel is the EXACT function, which allows users to compare two text strings and determine if they are precisely the same. However, users may encounter issues where the Excel EXACT function is not working as expected, such as returning incorrect results or displaying errors. These issues can be caused by various factors such as extra space, spelling errors or different character cases, etc. In this article, we’ll explain these errors and provide solutions for troubleshooting these problems. The following image depicts an overview of the errors and solutions for the Excel EXACT function not working.

Overview of EXACT function not working in Excel


Introduction to Excel EXACT Function

The EXACT function in Excel compares two text strings and returns TRUE if they are exactly the same otherwise, it returns FALSE. The EXACT function is case-sensitive but it ignores formatting differences. The image below shows an overview of the syntax and usage of the EXACT function in Excel.

Introduction to EXACT function in Excel


3 Suitable Solutions to EXACT Function Not Working in Excel

The following sections will demonstrate 3 common problems and suitable solutions for these problems in Excel. Now, let’s get started.


Problem 1: Presence of Leading, Trailing, or Extra Spaces in String Input

It is very frequent to have additional spaces in strings while we type or copy or import them. Due to the presence of additional spaces in a string, we may get the output FALSE instead of TRUE. The following image shows a few of these cases where leading, trailing, or extra spaces in between words are present.

Result alteration due to the presence of extra spaces


Solution: Remove Leading, Trailing, or Extra Spaces using Excel TRIM Function

To troubleshoot this problem, we can remove the leading, trailing, or any other extra spaces from the argument strings by using the TRIM function.

To obtain proper results, we inserted the following formula in Cell D5 and then pressed Enter key.

=EXACT(TRIM(B5),TRIM(C5))
  • Later, we dragged the Fill Handle icon to copy the formula in the remaining cells. And, as we can see, after trimming the extra spaces, the EXACT function has returned TRUE instead of FALSE.

Using the TRIM function to remove extra spaces

Formula Breakdown

  • TRIM(B5)

It trims the leading, trailing, or extra spaces in between words from the first input string.

  • TRIM(C5)

It trims the leading, trailing, or extra spaces in between words from the second input string.

  • EXACT(TRIM(B5),TRIM(C5))

Finally, the EXACT function compares the trimmed strings and returns TRUE or FALSE.


Problem 2: Use of Different Character Cases in String Input

Since the EXACT function is case-sensitive, it will return FALSE even if the character case of similar strings is not the same. The following image shows a few of these cases where the EXACT function has returned FALSE due to different character cases.

Output alteration due to string character case being different


Solution: Use UPPER, LOWER, or PROPER Functions to Standardize Character Cases

A remedy to this problem is to use the UPPER, LOWER, or PROPER functions for the input strings to standardize their character cases.

Standardization of character case in the input string

To obtain the required output, we can use any of the following 3 formulas and press the Enter key.

Formula 1:

=EXACT(LOWER(B5),LOWER(C5))

Formula 2:

=EXACT(UPPER(B5),UPPER(C5))

Formula 3:

=EXACT(PROPER(B5),PROPER(C5))
  • Later, use the Fill Handle tool to copy the formula in the remaining cells of Column D.

Formula Breakdown

  • LOWER(B5)

It converts the first input text string to lowercase

  • LOWER(C5)

It converts the second input text string to lowercase.

  • EXACT(LOWER(B5),LOWER(C5))

Then, the EXACT function compares the lowercase text strings and returns TRUE or FALSE.

  • EXACT(UPPER(B7),UPPER(C7))

This formula works the same as the previous one, except the input strings are converted into uppercase because of the UPPER function.

  • EXACT(PROPER(B9),PROPER(C9))

And, in this case, the strings are converted into proper sentence format (capitalizes the first letter of the string).


Problem 3: Spelling Error in Function Arguments

Spelling errors in input arguments may also result in an alteration of the desired output. In the following image, the EXACT function has returned FALSE as the input strings are not the same due to spelling errors.

Result alteration due to spelling errors


Solution: Verify Spelling of the Arguments

There is no direct way to troubleshoot this problem. We have to carefully verify the correct spelling for the input arguments.

Verifying correct spelling for the desired result


How to Compare Multiple Cells with EXACT Function in Excel

Although the EXACT function compares two strings only, we can compare multiple strings stored in multiple cells by combining the AND function with it. If all the arguments of AND Function evaluate to TRUE, then it returns TRUE. Otherwise, it returns FALSE.

To compare the multiple texts listed in every row of this dataset, insert the following formula in Cell F5 and press Enter key.

=AND(EXACT(B5:E5,B5))
  • Afterward, copy the formula in the remaining cells by dragging the Fill Handle

Comparing multiple strings by combining AND and STRING functions

Formula Breakdown

  • EXACT(B5:E5,B5)

First, the EXACT function compares each string in range B5:E5 with the string in Cell B5. For an exact match, it returns TRUE. Otherwise, it returns FALSE.

  • AND(EXACT(B5:E5,B5))

If every output from the EXACT function for the range B5:E5 is TRUE then AND function returns TRUE. Else it returns FALSE.


Things to Remember

  • The EXACT function is case-sensitive, but it ignores the formatting differences.
  • Extra spaces (leading, trailing, or in between words) in the input string result in a different output.
  • By combining the AND function with the EXACT function, we can compare multiple strings.

Download Practice Workbook

You may download the following file and practice yourself.


Conclusion

This concludes our article to learn about the solutions of Excel EXACT function not working. We demonstrated 3 suitable problems and their solutions here. We hope these solutions will work as remedies for your problems with the Excel EXACT function not working. Feel free to leave your thoughts on the article in the comment section.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Seemanto Saha
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He's been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on ExcelDemy Forum where he solved 50+ Excel-based user problems. Currently, he is working as a team leader for ExcelDemy. He guides his team to write reader-friendly content. His area of interests are Excel Data Analysis, Excel Dashboards and VBA Programming.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo