How To Compare Two Cells in Excel (10 Easy Methods)

There are many ways to compare cells in Excel. You can compare two cells and find matches, differences, and some other operations using Excel. In this article, we will discuss several easy and handy methods to compare cells.


Download Excel Workbook

You can download the practice workbook that we have used to prepare this article.


10 Easy Methods to Compare Two Cell in Excel

1. Compare Two Cells Side by Side Using the Equal to Sign

Using this method, you can find whether two cells contain similar data or not. Besides, this method compares data regardless of their type. For instance, we want to compare column Name 1 to column Name 2. Here are the following steps:

📌 Step 1: 

  • Type the following formula in Cell D5 (to compare B5 & C5).
=B5=C5

Compare Two Cells Side by Side Using the Equal to Sign

📌 Step 2:

  • Drag down the Fill Handle (+) to copy the formula to the rest of the cells.

Read More: Compare Two Cells in Excel and Return TRUE or FALSE (5 Quick Ways)


2. Use IF Function to Compare Two Cells

Comparing using the IF function is very handy. Likewise, equal to sign, you can find matches/mismatches using this function. In our example, we will match column List 1 and column List 2.

📌 Step 1:

  • Insert IF function in Cell D5 and select the arguments.
=IF(B6=C6,"Match","Not a Match")

Use IF Function to Compare Two Cells in Excel

📌 Step 2: 

  • Entered the above-mentioned formula you will get the following result.  Finally, drag down the Fill Handle (+) of Cell D5 to copy the formula to the rest of the cells.

Read More: Return YES If 2 Cells Match in Excel (10 Methods)


3. Insert Excel EXACT Function to Compare Two Cells

Sometimes, cells contain text both in uppercase and in lowercase style. If you want to find if both the cells are containing uppercase or lowercase text then the EXACT function would be a great help.

📌 Step 1:

  • To compare Cell B5 and Cell C5, type EXACT function select necessary cells for arguments.     
=EXACT(B5,C5)

Insert Excel EXACT Function to Compare Two Cells

📌 Step 2:

  • Upon entering the formula, you will get the following result. Later, drag down Fill Handle (+) of Cell D5 to copy the formula to the rest of the column.

Read More: Excel Compare Two Strings for Similarity (3 Easy Ways)


4. Combine IF and EXACT Functions to Compare Two Cells in Excel

You can compare two cells using the combination of IF and EXACT functions. Besides, the combination of these two functions is more effective.  Because, the EXACT function checks the exactness of the data, and the IF function returns the condition of the data.

📌 Step 1:

  • To compare Cell B5 and Cell C5, enter the formula combining both function:
=IF(EXACT(B5,C5), "Match","")

Combine IF and EXACT Functions to Compare Two Cells in Excel

📌 Step 2:

  • After entering the formula, you will get the following result.


5. Highlight Matching Data to Compare Two Cells

Suppose, you have two different sets of data in excel and you want to analyze matched values in cells. Conditional formatting offers great help in solving such situations. Moreover, you can highlight matched cells very easily by using this method.

📌 Steps:

  • Select the dataset.

Highlight Matching Data to Compare Two Cells

  • Go to Home > Conditional Formatting from Styles group.

Highlight Matching Data to Compare Two Cells

  • Click New Rule From Conditional Formatting.

Highlight Matching Data to Compare Two Cells

  • A new dialogue box will show up. Select the rule “Use a formula to determine which cells to format”.

Highlight Matching Data to Compare Two Cells

  • Type the following formula in the description box.
=$B5=$C5

Highlight Matching Data to Compare Two Cells

  • Click the Format button, go to the Fill tab and choose the color.  Then, click OK.

Highlight Matching Data to Compare Two Cells

Finally, If you follow the above steps correctly, all matched cells in the two columns will be highlighted. Conversely, differently named rows would not be highlighted.

Read More: Compare Two Cells Using Conditional Formatting in Excel (3 Methods)


6. Compare and Highlight Two Cells with Unique Data in Excel

Similar to the previous method, by using Conditional Formatting, you can compare two cells in various ways. For example, you can find unique values between two datasets.

📌 Steps:

  • Select the dataset.

Compare and Highlight Two Cells with Unique Data in Excel

  • Go to Home > Conditional Formatting from Styles group.

Compare and Highlight Two Cells with Unique Data in Excel

  • Press the Duplicate Values option from Highlight Cell Rules.

Compare and Highlight Two Cells with Unique Data in Excel

  • Subsequently, the Duplicate Values dialogue box will show up. Choose the Unique option from the drop-down.

Compare and Highlight Two Cells with Unique Data in Excel

  • You can also choose the color of the highlight from the drop-down by using the Custom Format option.

Compare and Highlight Two Cells with Unique Data in Excel

  • Then, enter OK.

Compare and Highlight Two Cells with Unique Data in Excel

And, the final output is, all the unique values between cells are highlighted.

Read More: How to Compare Two Cells and Change Color in Excel (2 Ways)


7. Use LEFT & RIGHT Functions to Compare Two Cells Partially 

Sometimes, you might need to match two cells partially. For example, you may need to compare only the first or last 3 characters of the cell. In those situations, LEFT or RIGHT functions can be used. The LEFT function returns the specified number of characters from the start of a text string. And similarly, the RIGHT function returns the characters from the right. In our example, we will match 3 Leftmost/Rightmost characters.

7.1. Compare Using LEFT Function

📌 Step 1:

  • To match first 3 characters of Cell B5 and Cell C5, here is the formula using LEFT Function:
=LEFT(B5,3)=LEFT(C5,3)

Use LEFT & RIGHT Functions to Compare Two Cells Partially

📌 Step 2:

  • After entering the above formula correctly, the following is the output. Click Fill Handle (+) of Cell D5 to copy the formula to the rest of the column.


7.2. Compare using RIGHT Function

📌 Step 1:

  • To match the last 3 characters of the Cell H5 and Cell  I5, Insert RIGHT Function and select or type arguments. Here is the Formula:
=RIGHT(H5,3)=RIGHT(I5,3)

Use LEFT & RIGHT Functions to Compare Two Cells Partially

📌 Step 2:

  • After you enter the above formula, the following is output. Click the Fill Handle (+) of Cell D5 to copy the formula to the rest of the column.


8. Using VLOOKUP and Find Matches in Excel

The VLOOKUP function is one of the easy ways to compare cells. It is commonly used to analyze Excel data. The VLOOKUP function looks for a value in the leftmost column in a table and then returns a value in the same row from the specified column. If you want to find any value to a column VLOOKUP function can be used.

📌 Step 1:

  • If we want to match the value of Cell C5 in Column Name 1, then the formula will be:
=IFERROR(VLOOKUP(C5,$B$5:$B$11,1,0),"No Match")

Compare Cells Using VLOOKUP and Find Matches in Excel

Breakdown of the Formula:

  • VLOOKUP(C5,$B$5:$B$11,1,0)

Here, the VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.  So, the function will look for C5 in the range B5:B11 and return:

{John}

Conversely, when the function will find C6 in range B5:B11, it will return a #N/A error because C6 is not present in the prescribed range.

  • IFERROR(VLOOKUP(C5,$B$5:$B$11,1,0), “No Match”)

The IFERROR function returns value_if_error if the expression is an error and the value of the expression itself otherwise. In our example, we have put “No Match” as an argument. As a result, when we will look for C6 in the above-mentioned range, the formula returns:

{No Match}

📌 Step 2:

  • After entering the formula, you will get the matched name in a 3rd column. Fill Handle (+) is used to copy the formula for the rest of the cells.


9. Using VLOOKUP and Find Differences

VLOOKUP can also be used to find differences between cells. the VLOOKUP function, in combination with IF & ISERROR function, finds a particular value in a range of data and returns the differences/similarity as output.

📌 Step 1:

  • If we want to find data in Cell C5 in Column Name 1, then the formula will be:
=IF(ISERROR(VLOOKUP(C5,$B$5:$B$11,1,0)),"Not Available","Available")

Compare Cells Using VLOOKUP and Find Differences

Breakdown of the Formula:

  • VLOOKUP(C5,$B$5:$B$11,1,0)

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.  So, the function will return:

{John}

Unfortunately, this is not the ultimate result we want from this method. We want to know whether any value is present in a range or not. So, the next part of the formula is:

  • ISERROR(VLOOKUP(C5,$B$5:$B$11,1,0))

Here, the ISERROR function checks whether a value is an error, and returns TRUE or FALSE. So, for D5, the function will find the value of  C5 in the range B5:B11 and return:

{FALSE}

The reason is, C5 is present in the mentioned range. Likewise, for other cells when an error will be found, it will return “TRUE”.

  • IF(ISERROR(VLOOKUP(C5,$B$5:$B$11,1,0)),”Not Available”,”Available”)

Now comes the final part. The IF function checks whether a condition is met, and returns one value if true, and another value if false. We put “Not Available” and “Available” as arguments. Finally, for D5, the function returns:

{Available}

📌 Step 2:

  • After entering the formula you will find the differences in the Output Column. Fill Handle (+) is used to copy the formula for the rest of the cells.


10. Compare Two Cells with Greater Than or Less Than Criteria

Sometimes, you may need to compare two cells in excel to find which one is greater/lesser. For example, you can compare numbers, dates, etc. In such situations, we can use the IF function to do the comparison.

📌 Step 1:

  • In our dataset, if we want to compare between Cell B5 and Cell C5,  We have used following formula:
=IF(B5>C5,"Yes","No")

Compare Two Cells with Greater Than or Less Than Criteria

📌 Step 2:

  • After entering the formula, here is the result. In our dataset, the date in Cell B5 is not greater than the date in Cell C5 so the output is No.


Conclusion

There are many more ways to compare two Excel cells, but in this article, we tried to discuss easier methods. All these methods are easy to understand and take less time. If you have any questions regarding this article, please let us know.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

2 Comments
  1. In “10. Compare Two Cells with Greater Than or Less Than Criteria”, I would like to determine the actual numerical difference between the two cells and the percentage of that difference to one of the cells. Eg., if one cell is 2 and the other cell is 10 … the difference is 8, which is 80% less than 10 (10 would be the mean of my interested dataset). How to I go about writing the formula?
    Thank you.

    J

    • Hi J,
      Hope you are doing well.
      You can get the actual numerical difference between two cells by using subtraction (-) in the formula. In case of, percentage value use the formula given below.
      =IF(AND(0<D5,D5<$D$10),(D5/$D$10)*100&"% Less Than 10",(-D5/$D$10)*100&"% Greater Than 10")

      If the condition is True then the formula will return the result as follows.

      1.compare two cells

      On the other hand, if the condition is False then the formula will return the result as follows.

      2.Compare Two Cells

      Here, the AND function will return True if both the conditions are true else it will return False.
      Thanks.

      Regards,
      Arin Islam

Leave a reply

ExcelDemy
Logo