# 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.

## 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. 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. • 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. ### 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. • Then, drag cell E5 to the end of your dataset. It will apply the same formula in all other cells in column E. • 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. • Afterward, drag cell E5 to the end of your dataset.
• consequently, it will apply the same formula in all other cells in column E. • 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 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. • Afterward, drag cell E5 to the end of your dataset.
• Consequently, it will apply the same formula in all other cells in column E. • 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 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. • Afterward, drag cell E5 to the end of your dataset.
• Consequently, it will apply the same formula in all other cells in column E. • 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 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. • Afterward, drag cell E5 to the end of your dataset. It will apply the same formula in all other cells in column E. • 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. • Now the Go To Special box will appear.
• Then, select Row Difference and click 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. ### 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. • Afterward, drag cell E5 to the end of your dataset.
• Therefore, it will apply the same formula in all other cells in column E. • 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.

## 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. #### Prantick Bala

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 Advanced Excel Exercises with Solutions PDF  