Comparing Two Columns and Returning 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.

How to Compare Two Columns and Return Common Values in Excel 

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.

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-

⏩Press the Enter button.

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

Now take a look at the image below and 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

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–

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

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-

⏩Press the Enter button.

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

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”

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: Using IF, ISNUMBER, MATCH Functions to Compare Two Columns and Return Common Values in Excel

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–

⏩Click the Enter button on your keyboard.

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

Now take a look at what 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: Excel formula to compare two columns and return a value 

Method 2: Applying Excel Conditional Formatting with Built-in Rules

Conditional Formatting is a very 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.

Step 2:

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

⏩Finally, just press OK.

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

Method 3: Using Conditional Formatting with New Rules to Return Common Values (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.

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.

⏩Then click Format.

Then ‘Format Cells’ dialog box will appear.

Step 3:

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

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

Step 4:

⏩ At this moment, just press OK.

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

Method 4: Applying Boolean Logic to Compare and Return Common Values from the 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–

⏩Hit the Enter button to get the output.

Step 2:

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

Now you will get the output as we wanted.

Method 5: Returning Common Values from the Same Row with Excel IF Function

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-

⏩Click the Enter button on your keyboard.

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

Now you will spot that our operation is done.

Download Practice Workbook

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

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.

<< Go Back to Columns | Compare | Learn Excel