In this article, we will discuss several easy methods in Excel to **find matching values** in two columns. Sometimes, while working in Excel, we need to find matches or mismatches between columns. Fortunately, Excel offers some quick and easy ways to do the comparison. Such as, we can use **Conditional Formatting**, **functions**, **formulas,** etc. to find match/mismatch.

**Download the Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**8 Easy Methods for Excel Find Matching Values in Two Columns**

**1. Excel Find Matching Values in Two Columns Using IF Function**

One of the easiest ways to search matches between Excel columns is to use **the IF function**. For example, we have two lists of fruit names, and here are the steps to find matching fruit names between **List 1** and **List 2**.

**Steps:**

- First, type the following formula in
**Cell C5**.

`=IF(B5=C5,"Match","Not a Match")`

Here, the** IF** function checks whether a condition is met, and returns the value if **TRUE**, and another value if **FALSE.**

- Next, we will get the result according to the data of the list. Use the
**Fill handle**(**+**) tool to copy the formula to the rest of the cells.

**Read More: Excel VBA to Match String in Column (5 Examples)**

**2. Combination of IF and EXACT functions to Get Matching Values in Two Columns(Case Sensitive)**

We can combine the **IF** and** EXACT** functions to find the matching data between columns. **The EXACT function** is case-sensitive. So, this combination of functions only looks for case-sensitive matched data between columns.

**Steps:**

- Firstly, type the below formula in
**Cell C5**.

`=IF(EXACT(B5,C5), "Match","Not a Match")`

Here, the **EXACT** function checks whether two text strings are exactly the same, and returns **TRUE** or **False**. And, the **IF** function returns ‘**Match**’ or ’**Not a Match**’ upon comparing two columns.

- In the end, if the formula is entered correctly, the following will be the output.

**Read More:** **How to Find Case Sensitive Match in Excel ( 6 Formulas)**

**3. Use of Excel IF, AND/OR Combination to Find Matching Values in Two Columns**

In this method, we will combine the **IF** and **OR** functions to get the matching value between two columns. **The OR function **checks whether any of the arguments are **TRUE**, and returns **TRUE** or **FALSE**. This function returns **FALSE** if all the arguments are **FALSE**.

**Steps**:

- Initially, type the below formula in
**Cell C5**.

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

Here, the** OR** function checks whether **B5** and **C5** are equal, and then the **IF** function returns ‘**Match**’ if columns contain similar data, otherwise it returns blank (**“”**).

- Finally, here is the output according to the typed formula.

**4. Find Matching Values in Two Columns Excel with Combination of IF and COUNTIF Functions**

Likewise, in the previous **two methods**, we can use **the COUNTIF function** along with Excel **IF **function. The** COUNTIF** function counts the number of cells within a range that meets the given condition For instance, we have two lists of people’s names and we want to find whether one list name matches the other. Following will be the steps.

**Steps:**

- Type the following formula at first.

`=IF(COUNTIF($C$5:$C$8,$B5)=0,"Not a Match","Match")`

Here, the **COUNTIF** function searches for the value of **Cell B5 **in range **C5:C8**. And, like previous methods, the** IF** function returns “**Match**” or “**Not a Match**” upon comparing the data between columns.

- Finally, the result will be the following.

**Similar Readings**

**Copy Values to Another Cell If Two Cells Match in Excel: 3 Methods****How to Vlookup and Pull the Last Match in Excel (4 Ways)****Excel VBA to Match Value in Range (3 Examples)**

**5. Use Highlight Cell Rules to Find Matching Values in Two Columns in Excel**

We can use different options of **Conditional Formatting** to get matching values between two Excel columns. For example, in this method, we will use the ‘**Highlight Cell Rules**’ option. So, let’s go through the steps.

**Steps:**

- Firstly, select the entire dataset (
**B5:C10**).

- Secondly, go to
**Home**>**Conditional Formatting**.

- Thirdly, go to
**Conditional Formatting**>**Highlight Cell Rules**>**Duplicate Values**.

- Next, the
**Duplicate Values**window will show up. Now, Make sure you select the ‘**Duplicate**’ option from the drop-down. Then, choose the highlight color or you can choose the color from ‘**Custom Format**’. After that, click**OK**.

- Finally, matched names between the columns will be highlighted.

**6. Get Matching Values in Two Columns Using Conditional Formatting (Arithmetic Formula)**

Similar to **Method 5**, now we will use another option of** Conditional Formatting** to find matching values in two columns in Excel. Let’s go through the steps.

**Steps:**

- First, select the entire dataset (
**B5:C10**).

- Next, go to
**Home**>**Conditional Formatting >****New Rule**.

- Then, the ‘
**New Formatting Rule**’ window will show up. Choose the ‘**Use a formula to determine which cells to format**’ option. - After that, type the following formula in ‘
**Format values where this formula is**true:’field and click on the**Format**

`=$B5=$C5`

- Click the ‘
**Fill**’ tab and choose the highlight color and click**OK**.

- Again click
**OK**

- Finally, matched fruit names will be highlighted.

**7. Apply IFERROR and VLOOKUP Functions Combination to Search Matching Values in Two Columns in Excel**

Now, in this method, we will apply **the VLOOKUP function **along with **the IFERROR function** to search matching values between columns. The **VLOOKUP** function looks for a value in the leftmost column in a table and then returns a value in the same row from the specified column. So, here are the steps

**Steps:**

- First, type the following formula in
**Cell C5**.

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

- Lastly, the formula will return as follows:

**Breakdown of the Formula**:

➤ **VLOOKUP(C5,$B$5:$B$11,1,0)**

Here, the **VLOOKUP** function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. So, the function will look for **C5** in the range **B5:B11** and return:

{John}

Conversely, when the function will find **C6** to range **B5:B11**, it will return a **#N/A **error because **C6** is not present in the prescribed range.

➤ **IFERROR(VLOOKUP(C5,$B$5:$B$11,1,0),”No Match”)**

The **IFERROR** function returns value_if_error if the expression is an error and the value of the expression itself otherwise. In our example, we have put **“No Match” **as an argument. As a result, when we will look for **C6** in the above-mentioned range, the formula returns:

{No Match}

**Read More:** **How to Match Data in Excel from 2 Worksheets**

**8. Search Matching Values with Combination of INDEX and MATCH Functions in Excel**

Often, we have to compare data between two columns of the two different tables and match them. In that case, the combination of **the MATCH function** and **the INDEX Function** can be a great help. For example, we have two tables where one column is common; that is ‘**Fruit Name**’. Now, we will search matched fruit ‘**Quantity**’ between these tables

**Steps:**

- Type the below formula in
**Cell F5**.

`=INDEX($C$5:$C$12,MATCH($E5,$B$5:$B$12,0))`

- Upon entering the formula correctly, the following will be the output.

**Breakdown of the Formula:**

➤** MATCH($E5,$B$5:$B$12,0)**

Here, the **MATCH** function returns the relative position of the value of **Cell E5** in the array (**B5:B12**) that matches a specified value in a specified order.

➤ **INDEX($C$5:$C$12,MATCH($E5,$B$5:$B$12,0))**

And, here the **INDEX** function returns a value or reference of the cell at the intersection of a particular row and column, in a given range (**C5:C12**) and thus replying the ‘**Quantity**’ of the fruit accordingly.

**Read More:** **How to Return Row Number of a Cell Match in Excel (7 Methods)**

**Conclusion**

In the above article, I have tried to discuss all the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.