How to Compare Two Columns and Return Common Values in Excel

Microsoft Excel offers several options for comparing columns in MS Excel, but most of them are for searching in one column. This article will help you to apply several techniques to compare two columns in Excel and return common values.


Download Practice Book

You can download the free Excel template from here and practice on your own.


3 Excel Tricks to Compare Two Columns and Return Common Values

Let’s get introduced to our dataset first. I have placed some States in two columns that represent participants of a basketball tournament for two consecutive years. Look that some States are common. Now, we’ll compare them and return common values using 3 quick tricks in Excel.

Sample Dataset: 3 Excel Tricks to Compare Two Columns and Return Common Values

Trick #1 IF + COUNTIF

If we want to check each cell between two columns then we can use the IF function and the COUNTIF function together. We will check if the values in Column B are in Column C or not. The COUNTIF is used to count cells in a range that meets a single condition.

Steps:

⏩By activating Cell D5 type the formula given below-

=IF(COUNTIF($C$5:$C$12, $B5)=0, "No match in C", "Match")

⏩Press the Enter button.

⏩Finally, use the Fill Handle tool to copy the formula for the other cells.

Compare Two Columns and Return Common Values(IF + COUNTIF)

Now take a look at the image below that we have found our desired output.

Formula Breakdown:

COUNTIF($C$5:$C$12, $B5)=0

The COUNTIF function will check the value of Cell B5 through the range C5:C12 whether it is equal or not. If equal then it will return 1 otherwise 0. So the output will return as-
FALSE

IF(COUNTIF($C$5:$C$12, $B5)=0, “No match in C”, “Match”)

Finally, the IF function will show ‘No match in C’ for FALSE and ‘Match’ for TRUE. It will return as-
Match

Read more: Excel formula to compare two columns and return a value


Trick #2 IFERROR + VLOOKUP

In this method, we’ll check the values of Column D in Column C if there is any match or not using the IFERROR and VLOOKUP Functions. If a match is found then it will return the state name otherwise will show No Match. The IFERROR function is used to trap and manage errors in formulas and calculations. The VLOOKUP function is used to look up a value in the leftmost column of a table and returns the corresponding value from a column to the right.

Steps:

⏩Type the given formula in Cell D5

=IFERROR(VLOOKUP($C5,$B$5:$B$12,1,0),"No Match")

⏩Then just hit the Enter button and use the Fill Handle icon to copy the formula.

Compare Two Columns and Return Common Values(IFERROR + VLOOKUP)

Here’s our output-

Formula Breakdown:

VLOOKUP($C5,$B$5:$B$12,1,0)

The VLOOKUP function will check Cell C5 through the range B5:B12. If it finds a common value then it will show that value otherwise will show #N/A. So it returns for Cell C5
#N/A

IFERROR(VLOOKUP($C5,$B$5:$B$12,1,0),”No Match”)

Then the IFERROR function will show “No Match” for #N/A and the other output will remain the same. For Cell C5 it will return as-
“No Match”


Trick #3 IF + ISERROR + VLOOKUP

Let’s use another combination of functions to compare two columns in excel and return common values like the previous method. Which are the IF, ISERROR, and VLOOKUP functions. The ISERROR function in Excel checks whether a value is an error and returns TRUE or FALSE.

Steps:

⏩After activating Cell D5 type the formula given below-

=IF(ISERROR(VLOOKUP($C12,$B$5:$B$12,1,0)),"No Match","Match")

⏩Press the Enter button.

⏩Then use the AutoFill option to get all the results.

Compare Two Columns and Return Common Values (IF + ISERROR + VLOOKUP)

Now you will see that we have found our common values.

Formula Breakdown:

VLOOKUP($C5,$B$5:$B$12,1,0)

The VLOOKUP function will check Cell C5 through the range B5:B12. If it finds a common value then it will show that value otherwise will show #N/A. So it returns for Cell C5
#N/A

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

Now the ISERROR function will show TRUE for #N/A and FALSE for other output that will return as-
TRUE

IF(ISERROR(VLOOKUP($C5,$B$5:$B$12,1,0)),”No Match”,”Match”)

Finally, the IF function will return “No Match” for TRUE and “Match” for FALSE. So it will return-
“No Match”


5 Methods to Compare Two Columns in Excel

Well, returning common values is not always necessary. Sometimes you might just want to highlight the common values. There are some pretty effective and easy ways in Excel with which you can compare two columns in your dataset. We will discuss them in the following one by one.

Method 1: IF + ISNUMBER + MATCH

In our very last method, we’ll use the IF, ISNUMBER, and MATCH Functions to do the same operation. The ISNUMBER function will return TRUE when a cell contains a number, and FALSE if not. The MATCH function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

Steps:

⏩Write the given formula in Cell D5

=IF(ISNUMBER(MATCH($C5,$B$5:$B$12,0)),"Match","No Match")

⏩Click the Enter button on your keyboard.

⏩Finally, use the Fill Handle icon to copy the formula for the rest of the cells.

Compare Two Columns in Excel (IF + ISNUMBER + MATCH)

Now take a look that our operation is done with the functions.

Formula Breakdown:

MATCH($C5,$B$5:$B$12,0)

The MATCH function will return the relative position number of matched values in the range B5:B12. If doesn’t match then it will show #N/A
#N/A

ISNUMBER(MATCH($C5,$B$5:$B$12,0))

The ISNUMBER function will show TRUE for numbers and FALSE for errors. That will return as-
FALSE

IF(ISNUMBER(MATCH($C5,$B$5:$B$12,0)),”Match”,”No Match”)

Finally, the IF function will show “Match” for TRUE and “No Match” for FALSE
“No Match”

Read more: How to Compare Two Columns or Lists in Excel


Method 2: Conditional Formatting with Built-in Rules

Conditional Formatting is a too common and easy way to compare common values. It will highlight all the common values with the selected color. Let’s see how to use it.

Step 1:

⏩ Select the data range B5:C12

⏩Then click as follows: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

A dialog box will open up.

Compare Two Columns in Excel (Conditional Formatting with Built-in Rules)

Step 2:

⏩ Select the Duplicate option and desired color from the Format cells that contain box.

⏩Finally, just press OK.

Conditional Formatting with Built-in Rules

Now you will see that all the common values are highlighted with the selected color.


Similar Readings:


Method 3: Conditional Formatting with New Rules (Same Row)

Here, we’ll again apply Conditional Formatting for a specific condition. We’ll find common values in the same row only.

Step 1:

⏩ Select the data range B5:C12

⏩Then click as follows: Home > Conditional Formatting > New Rule

A dialog box will appear.

Compare Two Columns in Excel (Conditional Formatting with New Rules)

Step 2:

⏩ Press Use a formula to determine which cells to format from the Select a Rule Type box.

⏩Later, type the formula given below in Format values where this formula is true box.

=$B5=$C5

⏩Then click Format.

Then ‘Format Cells’ dialog box will appear.

Conditional Formatting with New Rules

Step 3:

⏩ Choose your desired color from the Fill option. I have chosen lite green color.

⏩Press Ok and it will go back to the previous dialog box.

Conditional Formatting with New Rules

Step 4:

⏩ At this moment, just press OK.

Conditional Formatting with New Rules

Now you observe that all the common values in the same row are now highlighted with picked color.


Method 4: Boolean Logic (Same Row)

We can find common values in the same row while comparing two columns using Equal Sign(=) too. If the same values are found then it will show TRUE otherwise FALSE. To show the output I have added a new column named ‘Remark’.

Step 1:

⏩ Write the formula in Cell D5

=B5=C5

⏩Hit the Enter button to get the output.

Compare Two Columns in Excel :Boolean Logic (Same Row)

Step 2:

⏩ Now double-click the Fill Handle icon to copy the formula.

Boolean Logic (Same Row)

Now you will get the output as we wanted.


Method 5: The IF Function (Same Row)

If we use the IF function for the previous operation then we can get the output with our manually selected text. The IF function checks whether a condition is met and returns one value if true and another value if false. Here, It will show ‘Match’ if it gets common values in the same row whether will show ‘No Match’

Steps:

⏩ Activate Cell D5.

⏩Then type the formula in it-

=IF(B5=C5,"Match","No Match")

⏩Click the Enter button on your keyboard.

⏩Lastly, use the Fill Handle tool to copy the formula.

Compare Two Columns in Excel :The IF Function (Same Row)

Now you will spot that our operation is done.


Conclusion

I hope all of the methods described above will be good enough to compare two columns in excel and return common values. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo