How to Compare Two Columns in Excel for Match (8 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to know how to compare two columns for match in Excel, you’ve come to the right place. We’ll show 8 suitable methods to compare two columns for match in Excel. This article will discuss every step of the methods. Let’s follow the complete guide to learn all of this.


Compare Two Columns in Excel for Match: 8 Easy Ways

Consider the following dataset. Here 10 days of sales data from two different salesmen are given. Each of them sold one car per day which is given in columns B and C. Now we will compare these two columns to find out which models are sold by both of them on the same day or on different days.

sample dataset

This section provides extensive details on these methods. Therefore, you should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Conditional Formatting to Compare Two Columns in Excel for Match

Using conditional formatting is the easiest way to compare two columns for a match.

📌 Steps:

  • First, select the cells you want to compare.
  • Then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicates Values.

select duplicate values to compare two columns for match in Excel

  • Consequently, the Duplicate Values box will appear.
  • Then, select Duplicate from the left side box and then click on OK.

  • Therefore, you can change the format by which the values will be highlighted from the right side box if you want.
  • Now, the Values that are common in both columns will be highlighted.

get the output to compare two columns for match in Excel


2. Finding Match in Two Columns by Simple Formula

You can compare two columns to find the match in the same row by using a simple formula.

📌 Steps:

  • First, to compare columns C and D, type the formula in any empty cell (E5),

=C5=D5

  • Next, press ENTER.
  • Now, if the  C5 and D5  cells have the same value E5 will show TRUE  and if the  C5 and D5  cells have different values, E5 will show FALSE.
  • Therefore, for our dataset, we have Toyota in cell C5 and Hundai in cell D5.  They are different, so cell E5 is showing false.

use simple formula to compare two columns in excel for match

  • Then, drag cell E5 to the end of your dataset. It will apply the same formula in all other cells in column E.

output

  • Look, we have the same value in cells C9 and D9, so cell E9 is showing TRUE. Similarly, we have the same value in cells C13 and D13, so cell E13 is showing TRUE.
  • Finally, all the true values indicate a match in both columns of the same row.

3. Compare Two Columns By VLOOKUP Function

You can compare two columns for any matches among any rows by using the VLOOKUP function.

📌 Steps:

  • First, type the following formula in cell E5,

=IFERROR(VLOOKUP(D5,$C$5:$C$14,1,0),"No Match")

  • Next, press ENTER.
  • Now, if D5 has the same value as any of the values in Column C, E5 will show the value and if D5 has a unique value, E5 will show No match.
  • Therefore, for our dataset Hundai is in cell D5  which is unique, so cell E5 is showing No match.

use VLOOKUP function to compare two columns in excel for match

  • Afterward, drag cell E5 to the end of your dataset.
  • consequently, it will apply the same formula in all other cells in column E.

output

  • Finally,  the values in cells D7, D9, and D13 have a match with column C.
  • As a result, cells E7, E9, and E13 are showing matching values.

4. If Function for Comparing Two Columns in Excel

You can compare two columns to find the match in the same row by using the IF function.

📌 Steps:

  • First, to compare columns C and D, type the formula in any empty cell (E5),

=IF(C5=D5, "Match", "Mismatch")

  • Next, press ENTER.
  • Now, if the C5 and D5 cells have the same value E5 will show Match and if the  C5 and D5 cells have different values, E5 will show Mismatch.
  • For our dataset, we have Toyota in cell C5 and Hundai in cell D5.  They are different, so cell E5 is showing Mismatch.

 use IF function to compare two columns in excel for match

  • Afterward, drag cell E5 to the end of your dataset.
  • Consequently, it will apply the same formula in all other cells in column E.

output

  • Look, we have the same value in cells C9 and D9, so cell E9 is showing Match. Similarly, we have the same value in cells C13 and D13, so cell E13 is showing Match.

5. Compare Two Columns for Match by MATCH Function

We can also use the MATCH function to compare two columns to find matching values.

📌 Steps:

  • First, type the following formula in cell E5,

=NOT(ISNUMBER(MATCH(D5,$C$5:$C$14,0)))

  • Next, press ENTER.
  • Now, if  D5 has the same value as any of the values in Column C, E5 will show FALSE and if D5 has a unique value, E5 will show TRUE.
  • Therefore, for our dataset, Hundai in cell D5 is unique, so cell E5 is showing TRUE.

use MATCH function to compare two columns in excel for match

  • Afterward, drag cell E5 to the end of your dataset.
  • Consequently, it will apply the same formula in all other cells in column E.

output

  • Finally, the values in cells D7, D9, and D13 have a match with column C.
  • As a result, cells E7, E9, and E13 are showing the matching FALSE.

6. Insert INDEX Function to Compare Two Columns in Excel for Match

With the INDEX function, you can compare two columns to find a match in the same row.

📌 Steps:

  • First, type the formula in cell E5,

=INDEX(C5:C14,MATCH(D5,C5:C14,0))

  • Next, press ENTER.
  • Now, if the  C5 and D5 cells have the same value E5 will show the value and if the  C5 and D5  cells have different values, E5 will show #N/A.
  • Therefore, for our dataset, we have Toyota in cell C5 and Hundai in cell D5.
  • They are different, so cell E5 is showing #N/A.

use INDEX function to compare two columns in excel for match

  • Afterward, drag cell E5 to the end of your dataset. It will apply the same formula in all other cells in column E.

output

  • Look, we have the same value Audi in cells C9 and D9, so cell E9 is showing Audi.
  • Similarly, we have the same value Ford in cell C13 and D13, so cell E13 is showing Ford.

7. Compare Two Columns Through Go to Special Command

You can also compare two columns by using the Go to Special Command.

📌 Steps:

  • First, select the columns which you want to compare.
  • Then, go to Home > Editing > Find & Select > Go to Special.

 use go to special command to compare two columns in excel for match

  • Now the Go To Special box will appear.
  • Then, select Row Difference and click OK.

click on OK

  • Consequently, all the unique values in column D will be highlighted.
  • Therefore, you will find the match between two columns by looking at the non-highlighted cells.

final output


8. Compare Two Columns by EXACT Function

You can compare two columns to find the match in the same row by using the EXACT function.

📌 Steps:

  • First, to compare columns C and D, type the formula in any empty cell (E5),

=EXACT(C5,D5)

  • Next, press ENTER.
  • Now, if the C5 and D5 cells have the same value E5 will show TRUE and if the  C5 and D5 cells have different values, E5 will show FALSE.
  • Therefore, for our dataset, we have Toyota in cell C5 and Hundai in cell D5.
  • Hence, they are different, so cell E5 is showing false.

 use Exact Function to compare two columns in excel for match

  • Afterward, drag cell E5 to the end of your dataset.
  • Therefore, it will apply the same formula in all other cells in column E.

output

  • Look, we have the same value in cells C9 and D9, so cell E9 is showing TRUE.
  • Similarly, we have the same value in cells C13 and D13, so cell E13 is showing TRUE.
  • Finally, all the true values indicate a match in both columns of the same row.

Download Practice Workbook

You can download the following file to practice by yourself.


Conclusion

By applying any of the methods you will be able to compare two columns in Excel for Match. If you face any type of problem while comparing two columns in Excel please leave a comment. I’ll try my best to solve your problem.


<< Go Back to Columns | Compare | Learn Excel

What is ExcelDemy?

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

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo