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 a match/mismatch.

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

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

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

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

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

**8. Search Matching Values with a 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, 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.

**Download the Practice Workbook**

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

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

**<< Go Back to | Excel Match | Learn Excel**