EXACT

In the previous articles, we have discussed about how to match rows, how to match one excel column with another excel column in one Excel Workbook and between two different Workbooks. We used VLOOKUP, HLOOKUP, INDEX MATCH MATCH, we used VLOOKUP with MATCH function and so on to get the exact matching of data column wise.

In this article, we will discuss how to compare one cell’s data with another cell’s data to be equal.

If both the two cells contain numbers, then it is easy to compare because you can subtract one number from another number in the next cell and if the output or result is greater than zero then there is mismatch between two cells but you cannot compare strings or texts using this technique. 

You might have noticed while working with a large data that sometimes the content or data of one cell seems to be same or identical or equal to another cell’s data but these are not equal which may be a reason that VLOOKUP or other function is not working!!! Let us have an example to understand this situation.

Suppose we have a name in a cell, Johnny Depp (A former World No.1 American professional tennis player) and in the next cell it is Johnnny Depp, the same name. In this two cells is difficult to verify the error that one ‘n’ is extra in the second cell. Now imagine the difficulty to identify the mismatch in an Excel sheet which contains hundreds of names!!!

There may be another situation, Johnny Depp in one cell and JoHnny Depp in the next cell, now if you compare these two cells, you will find that in the second name Johnny, ‘h’ is capitalized.

Therefore, we need some excel formula or function to identify this type of problems in an Excel table.

Microsoft Excel has provided us EXACT function which can do the task very easily.

What is Exact function in Excel?

The Microsoft Excel EXACT function compares two strings between two CELLS and returns TRUE if both values are the same. Otherwise, it will return FALSE.

Syntax

The syntax for the EXACT function in Microsoft Excel is very simple, type

=EXACT(text1, text2)

Parameters or Arguments

text1 and text2
The two string values to compare.

Returns

The EXACT function returns TRUE if the two strings are the same.
The EXACT function returns FALSE if the two strings are different.

Please Note : The EXACT function is case-sensitive when it compares the two strings. If you need to compare two strings or text irrespective of case sensitivity, you must use another function nested and a little trick. Let us give few examples on this subjects.

Example 1 :

How to use EXACT function to compare two cells ?

In the below image, I need to check whether two cells have same data or not.

The task is simple, just in the next column (here E column, column head is ‘EXACT Function’), type =EXACT(C3,D3).

If the result is TRUE then two cells data are same else if the output is FALSE these cells contains different texts.

EXACT Function in Microsoft Excel

Example 2 :

Now if you look carefully you can notice that although the names are same in few cells (highlighted yellow) but the result of EXACT Function is FALSE. This is because EXACT function is Case sensitive function, if the texts or strings in the two cells are not similar in Case, EXACT function will return FALSE.

How to override the case sensitivity of EXACT function?

In this point, you need to be a little tricky and the trick is convert both the strings in either upper case or lower case by using UPPER function or LOWER function.

The excel formula becomes, =EXACT(UPPER(C4),UPPER(D4))

EXACT Function Without Case Sensitivity in Microsoft Excel

Note : There is an easy method you can implement in Excel to compare two cells without considering case sensitivity just type ‘=’ sign and put the first cell’s reference and again type ‘=’ and type the second cells reference like the below image.

Excel formula to compare cell between two Columns

Hope you have enjoyed this article.

Thank you for reading if you like this article, please share….