Excel Find Matching Values in Two Columns

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.

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.

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.

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.

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…

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

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

• 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