While doing different tasks in Excel we often come across a situation where the matching and differences of two or multiple columns are required. Itâ€™s not a difficult task to compare two columns or lists in Excel but you may get confused as there are so many ways to do it. In this article, we will look for different techniques for matching and differentiating columns in Excel.

**Table of Contents**Expand

**How to Compare Two Columns or Lists in Excel: 4 Easy Methods**

We have a dataset of two columns. Those columns contain names of items from two showrooms of a super shop. We will compare the data of these two showrooms.

**1. Compare Two Columns Using Equal Operator**

Here, we will compare two columns row-wise using the equal sign. When the items are the same indicate **True **otherwise **False**.

**ðŸ“Œ ****Steps:**

- Add a new column on the right side to show the matching status.

- Put the following formula on Cell
**D5**.

`=B5=C5`

- Now, press
**Enter**and drag the**Fill HandleÂ**icon.

We can see **True **appears for match cases otherwise, **False**.

**2. Use Row Differences Command of Go To Special Tool to Compare Two Lists in Excel**

In this method, we will use the Row differences technique. It compares those columns row-wise and selects the cells of the second column automatically.

**ðŸ“Œ ****Steps:**

- Select the whole dataset of Range
**B5:C9**. - Then, press the
**F5Â**button.

- Then, the
**Go To**dialog box appears. Click on the**SpecialÂ**option.

- Now, select the
**Row differences**option from the**Go To SpecialÂ**window. - Finally, press
**OK**.

- We can see two cells of the second column are selected.
- We change the color of the cells from the
**Fill ColorÂ**option.

- Look at the dataset now.

Cells of the second column with mismatched data are visible now.

**3. Use Excel Functions to Compare Two Columns or Lists in Excel**

**3.1 Using IF Function**

Here, we will use **the IF function**. It will compare the cells of the columns row-wise and check whether they are the same or not.

**IF**function checks whether a condition is met, and returns one value if

**TRUE,**and another value if

**FALSE**.

**ðŸ“Œ ****Steps:**

- We put a formula based on the
**IF**function on Cell**D5**.

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

This formula will check whether the cells are the same or not. If same then, show **Match **otherwise, **Mismatch**.

- Now, drag the
**Fill Handle**icon downwards.

You could use the following formula to compare for not matching values.

`=IF(B5<>C5,"Mismatch","Match")`

In this case, when the condition is **True, **it shows **Mismatch**, otherwise **Match**.

**3.2 Applying EXACT Function**

When we have the same data in two columns with case differences, we will use **the EXACT function **.

**EXACT**function checks whether two text strings are exactly the same, and returns

**TRUE**or

**FALSE.**It is

**case-sensitive**.

In Row 6**,** we have the same data from different cases. Now, apply the **EXACT **function to see whether it can detect the case difference or not.

**ðŸ“Œ ****Steps:**

- Insert the formula below on Cell
**D5**.

`=IF(EXACT(B5,C5),"Match","Mismatch")`

Here, the **IF **function is used to show the comment based on the decision taken by the **EXACT **function.

- Pull the
**Fill HandleÂ**icon.

We get the result. Due to the case difference, **Mismatch **is showing at Cell **D6**.

#### 3.3 Using MATCH Function

In this method, we will compare the 1st column with the 2nd column. When the matching of the 1st column is found on the 2nd column result will be **TRUE**.

Here, we will use **the MATCH function** with **ISERROR **and **IF **functions.

**MATCH**function returns the relative position of an item in an array that matches a specified value with a specified order.

**ðŸ“Œ ****Steps:**

- Put the following formula on Cell
**D5**.

`=IF(ISERROR(MATCH($B5,$C$5:$C$10,0)),"No match","Match found")`

When the statement isÂ true result will be **Match found** otherwise **No match**.

- Press
**Enter**to execute the formula.

We got the result based on the 1st column. We are looking for a match in the 2nd column.

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

**4. Compare Two Columns and Highlight Using Conditional Formatting**

In this section, we will use conditional formatting to compare two columns and highlight them according to conditions.

**4.1 Highlight Equal Values in Two Columns**

**ðŸ“Œ ****Steps:**

- Select the dataset first.
- Go to the
**Conditional Formatting**option from the**HomeÂ**tab. - Choose
**New Rule**from the appeared dropdown.

- The
**New Formatting Rule**window appears. - Select
**Use a formula to determine which cells to format**as the rule type. - Put the following formula on the marked box.

**Â**

`=$B5=$C5`

- Choose the
**Fill**tab from the**Format CellsÂ**window. - Choose the desired color.
- Press the
**OKÂ**button.

- We see the
**PreviewÂ**here. - Finally, press
**OK**.

- Look at the dataset.

Cells with the same data are the highlight.

**4.2 Highlight Unique and Duplicate Cells**

In this section, we will highlight the unique and duplicate data cells with different colors.

**ðŸ“Œ ****Steps:**

- Enter the
**New Rule**option as shown previously.

- Select
**Format only unique or duplicate values**rule type. - Choose the
**duplicateÂ**option.

- Then, set the format color and press
**OK**.

Duplicate cells are highlighted.

- Again, follow the previous process and choose the
**uniqueÂ**option.

- Have a final look at the dataset.

Duplicate and unique data care are highlighted differently.

**Compare Two Columns in Excel and Count Matches**

In this function, we will use the combination of **the SUMPRODUCT function**, and **the COUNTIF function** to count the matches. After that, we will calculate the number of total rows using **the ROWS function** and subtract the matches to get the number of mismatches.

**SUMPRODUCT**function returns the sum of products of corresponding ranges or arrays.

**ðŸ“Œ ****Steps:**

- First, we add two rows. One for the match and another for the mismatch.

- Now, insert the following formula based on the
**SUMPRODUCT**and**COUNTIF**function on Cell**C11**.

`=SUMPRODUCT(COUNTIF(B5:B9,C5:C9))`

- Press the
**Enter**button to get the result.

We get the number of matched rows.

- Now, go to Cell
**C12**and put the below formula.

`=ROWS(B5:C9)-C11`

- Again, press the
**Enter**button to get the number of mismatches.

**Compare Two Columns in Excel and Remove Duplicates**

In this method, we will show how to remove the duplicates after comparing two columns.

**ðŸ“Œ ****Steps:**

- Select the dataset first.
- Go to the
**Conditional FormattingÂ**section. - Choose
**Duplicate Values**from the**Highlight Cells Rules**.

- Choose a color to indicate the duplicates.

- We can see the color of cells containing duplicate data has been changed.
- Now, press
**Ctrl + Shift+ L**to enable the filter option.

- Click on the down arrow of the 2nd column.
- Choose the color of the duplicate cells from the
**Filter by colorÂ**section.

- Only duplicate values are showing now. Select that range.
- Press the right button of the mouse.
- Choose the
**Clear Contents**option from the**Context Menu**.

- Duplicate values are removed from the dataset.

- Again, go to the filter section and check the
**Select AllÂ**option.

- No duplicates are showing now.

**Excel Match Two Columns and Extract Output from a Third with VLOOKUP**

Here, we have two datasets. 1st one is of Showroom 1 and 2nd is of Showroom 2. We will compare the item columns of each dataset and extract the price from Showroom 1 to Showroom 2 using **the VLOOKUP function**.

**ðŸ“Œ ****Steps:**

- Apply the formula based on the
**VLOOKUP**function on Cell**F5**.

`=VLOOKUP($E5,$B$5:$C$10,2,FALSE)`

- After comparing Item (S1) with Item (S2), we extract the price in the 2ndÂ table.

**How to Compare More Than Two Columns in Excel**

In the previous sections, we have shown a comparison between two columns. When we have more than two columns, we can use the below methods.

**1. Use Excel AND Function**

**The AND function**Â checks whether all arguments are

**TRUE**, and returns

**TRUE**if all arguments are

**TRUE**.

In this method, after checking all the conditions, the result will be shown based on the comment used in the **IF **function. Before applying the formula, we add another column named Showroom 3.

**ðŸ“Œ ****Steps:**

- Now, put the formula on Cell
**E5**.

**Â Â**

`=IF(AND(B5=C5,C5=D5),"Match","Mismatch")`

- After that, pull the
**Fill HandleÂ**icon.

Finally, we get the status.

**2. Compare with Excel COUNTIF Function**

The **COUNTIF **function counts the number of cells within a range that meet the given condition, andÂ **the COUNTA function** counts the number of cells in a range that are not empty.[/wpsm_box]

Here, we will use these functions to compare multiple columns.

**ðŸ“Œ ****Steps:**

- Copy the following formula on Cell
**E5**.

`IF(COUNTIF(B5:D5,B5)=COUNTA(B5:D5),"Match","Mismatch")`

- Drag the
**Fill HandleÂ**icon.

Finally, we get the comparison result.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we described how to compare two columns or lists in Excel. We compared the columns row-wise and column-wise in both ways. I hope this will satisfy your needs. Please give your suggestions in the comment box if you have any. Goodbye!

**<< Go Back to Columns | Compare | Learn Excel**

Very informative and well described.

Thanks, Farhana for the feedback.

Excellent! Thanks for this useful information.

Thanks. Glad to know that you found it useful.