Prantick Bala
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.
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.
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.
- 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.
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.
- 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.
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.
- 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.
Read More: How to Match Two Columns and Return a Third in Excel
Similar Readings
- How to Compare Two Columns for Finding Differences in Excel
- Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)
- How to Compare Two Columns or Lists in Excel (4 Suitable Ways)
- Match Two Columns and Output a Third in Excel (3 Quick Methods)
- How to Compare Two Columns in Excel for Missing Values (4 ways)
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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
