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 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")

Excel Find Matching Values in Two Columns Using IF Function

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")

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

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", "")

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

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")

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

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


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

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

  • Secondly, go to Home > Conditional Formatting.

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

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

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

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

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

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

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

  • Next, go to Home > Conditional Formatting > New Rule.

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

  • 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

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

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

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

  • Again click OK

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

  • 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")

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

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

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

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


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo