Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook

You can download the following file to practice by yourself.


8 Easy Ways to Compare Two Columns in Excel for Match

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 which are common in both columns will be highlighted.

get the output to compare two columns for match in Excel

Read More: Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)


2. Finding Match in Two Columns by Simple Formula

You can compare two columns for finding 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.

Read More: How to Compare Text in Two Columns in Excel


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.

Read More: How to Count Matches in Two Columns in Excel (5 Easy Ways)


4. If Function for Comparing Two Columns in Excel

You can compare two columns for finding 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.

Read More: How to Match Two Columns and Return a Third in Excel


Similar Readings


5. Compare Two Columns for Match by MATCH Function

We can also use the MATCH function to compare two columns for finding 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.

Read More: Excel formula to compare two columns and return a value (5 examples)


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

With the INDEX function, you can compare two columns for finding 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.

Read More: Excel Formula to Compare and Return Value from Two Columns


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

Read More: How to Compare Two Lists and Return Differences in Excel


8. Compare Two Columns by EXACT Function

You can compare two columns for finding 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.

Read More: How to Compare Text Between Two Cells in Excel (10 Methods)


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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo