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.

**Table of Contents**Expand

**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-

`=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.

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**–

`=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.

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.

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**–

`=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.

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.

`=$B5=$C5`

⏩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**–

`=B5=C5`

⏩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-

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

⏩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**