{"id":1450,"date":"2021-02-01T16:17:08","date_gmt":"2021-02-01T15:17:08","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1450"},"modified":"2024-03-22T18:53:12","modified_gmt":"2024-03-22T17:53:12","slug":"match-two-lists-with-the-match-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/match-two-lists-with-the-match-function-in-excel\/","title":{"rendered":"Match Two Lists With The MATCH Function"},"content":{"rendered":"

You can quickly compare two lists in Excel<\/a><\/strong> for matches using the MATCH<\/strong> function, IF<\/strong> function, or highlighting row difference<\/strong>.<\/p>\n

Manually searching<\/strong> for the difference between two lists can both be time-consuming<\/strong> and prone to errors.<\/strong> You will end up wasting a lot of time<\/strong>!<\/p>\n

There are various inbuilt functions and features in Excel<\/strong> that can do this task of Excel compare two lists easily. Let’s look for various options<\/strong> that you can follow:<\/p>\n

Let’s look at each method one-by-one!<\/p>\n

 <\/p>\n

Don’t forget to download this Excel Workbook to follow along and compare two lists in Excel for matches:<\/em><\/h3>\n
\n

download excel workbook<\/strong><\/i><\/span>Match-Two-Lists.xlsx<\/span><\/a><\/p>\n<\/div>\n

 <\/p>\n

<\/a>Highlight Row Difference<\/strong><\/span><\/h3>\n

You can easily highlight differences in value in each row<\/strong> using the conditional formatting<\/strong> feature in Excel. It will provide you with an idea of how many lines in the columns differ in values.<\/p>\n

In the data below, you have two lists in Column A and Column B respectively.<\/p>\n

Follow the steps below<\/strong> to highlight row difference:<\/p>\n

STEP 1:<\/strong><\/span> Select both the columns.<\/p>\n

\"Match<\/a><\/p>\n

STEP 2:<\/strong><\/span> Go to Home<\/strong> > Find & Select<\/strong> > Go To Special<\/strong> or simply press keys Ctrl + G<\/strong> and Select Special<\/strong> to open the Go To Special dialog box.<\/p>\n

\"Match<\/a><\/p>\n

STEP 3:<\/strong><\/span> Select Row Difference<\/strong> and Click OK<\/strong>.<\/p>\n

\"Match<\/a><\/p>\n

And, Voila!<\/p>\n

\"Match<\/a><\/p>\n

All the values in Stock List 2 that do not match with the corresponding value in Stock List 1 have been highlighted.<\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span> You can mark these cells with color<\/a> as well. Go to Home<\/strong> > Font Color<\/strong>\u00a0> Select Red<\/strong>.<\/p>\n

\"Match<\/a><\/p>\n

\"Match<\/a><\/p>\n

This will permanently highlight the cells in red font color<\/strong>\u00a0for future reference.<\/p>\n

 <\/p>\n

<\/a>Compare Row using IF function<\/strong><\/span><\/h3>\n

You can use the IF Function<\/strong><\/a> to compare two lists in Excel for matches in the same row. If Function will return the value TRUE if the values match and FALSE if they don’t.<\/p>\n

You can even add custom text<\/a> to display the word “Match” when a criterion is met and “Not a Match” when it’s not met.<\/p>\n

Let’s see how we can compare two lists in Excel for matches using IF Function:<\/p>\n

STEP 1:<\/strong><\/span> We need to enter the IF function<\/strong> in a blank cell.<\/p>\n

=IF(<\/span><\/h3>\n

\"Match<\/a><\/p>\n

STEP 2:<\/strong><\/span> Enter the first argument<\/strong> for the IF function – Logical_Test<\/em><\/strong><\/span><\/p>\n

What is your condition?<\/strong><\/p>\n

The value in cell D12 is equal to the value in cell C12.<\/p>\n

=IF(D12=C12,<\/span><\/h3>\n

\"Match<\/a><\/p>\n

 <\/p>\n

STEP 3:<\/strong><\/span> Enter the second argument<\/strong> for the IF function – Value_if_true<\/em><\/span><\/strong><\/p>\n

What value should be displayed if the condition is true?<\/strong><\/p>\n

The text displayed should be Match\u00a0<\/strong>if D12 is equal to C12.<\/p>\n

=IF(D12=C12,<\/span>“Match”,<\/span><\/h3>\n

\"Match<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span> Enter the third argument<\/strong> for the IF function – Value_if_false<\/em><\/span><\/strong><\/p>\n

What value should be displayed if the condition is false?<\/strong><\/p>\n

The text displayed should be Not a Match\u00a0<\/strong>if D12 is not equal to C12.<\/p>\n

=IF(D12=C12,”Match”,<\/span>“Not a Match”)<\/span><\/h3>\n

\"Match<\/a><\/p>\n

 <\/p>\n

STEP 5:<\/strong><\/span> Apply the same formula to the rest of the cells by dragging the lower right corner downwards<\/strong>.<\/p>\n

\"Match<\/a><\/p>\n

 <\/p>\n

<\/a>Compare List using Match Function<\/strong><\/span><\/h3>\n

Before we understand how to compare two lists in Excel for matches, let’s first go through the basics of what the MATCH function<\/strong><\/a> Excel does.<\/p>\n

 <\/p>\n

What does it do?<\/strong><\/em><\/p>\n

It returns the position of an item in a range.<\/p>\n

Formula breakdown: <\/strong><\/em><\/p>\n

=MATCH(lookup_value<\/span>,\u00a0lookup_array<\/span>,\u00a0[match_type]<\/span>)<\/p>\n

What it means: <\/strong><\/em><\/p>\n

=MATCH(lookup this value<\/span>,\u00a0from this list or range of cells<\/span>,\u00a0return me the Exact Match<\/span>).<\/p>\n

\n

I am sure that you have come across many occasions where you have two lists of data<\/a> and want to know if a specific item in List1 exists in List2<\/strong>.<\/p>\n

Well, I have!<\/p>\n

With the MATCH function, you can verify if a cell\u00b4s item in List1 <\/em>exists in List2<\/em><\/strong>.<\/p>\n

The function will return the row position of that item in List2 <\/em>hence confirming that it exists<\/strong>.\u00a0 If you get a #N\/A it means that the cell\u00b4s item does not exist<\/strong> in List2<\/em>.<\/p>\n

You can then go ahead and filter your List1 <\/em>with either the values returned or the #N\/As<\/strong>.<\/p>\n

Here are our 2 Lists:<\/p>\n

\"Match<\/p>\n

\"Match<\/p>\n\n

STEP 1:<\/strong><\/span> We need to enter the MATCH function<\/strong> in a blank cell:<\/p>\n

=MATCH(<\/span><\/h3>\n

\"Match<\/p>\n

 <\/p>\n

STEP 2:<\/strong><\/span> Enter the first argument<\/strong> for the MATCH function –\u00a0Lookup_value<\/em><\/strong><\/span><\/p>\n

What is the value you want to check? <\/strong><\/p>\n

Select the cell containing the List1 value, as this is what we want to check against List2.<\/p>\n

=MATCH(C12,<\/span><\/h3>\n

\"Match<\/p>\n

 <\/p>\n

STEP 3:<\/strong><\/span> Enter the second argument<\/strong> for the MATCH function – Lookup_array<\/em><\/span><\/strong><\/p>\n

What is the list you want to check against? <\/strong><\/p>\n

Select the entire List2.<\/p>\n

\"Match<\/p>\n

And ensure to press F4<\/strong> to make it an absolute reference.<\/p>\n

=MATCH(C12, list2!$C$12:$C:21,<\/span><\/h3>\n

\"Match<\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span> Enter the third argument<\/strong> for the MATCH function –\u00a0Match_type<\/em><\/strong><\/span><\/p>\n

How specific is your matching? <\/strong>We want an exact match so place in 0.<\/p>\n

=MATCH(C12, list2!$C$12:$C:21,<\/span> 0)<\/span><\/h3>\n

 <\/p>\n

\"Match<\/p>\n

 <\/p>\n

STEP 5:<\/strong><\/span> Apply the same formula to the rest of the cells by dragging the lower right corner downwards<\/strong>.<\/p>\n

\"Match<\/p>\n

 <\/p>\n

You now have all of the results!<\/p>\n

You can see which row numbers the items exist in List2. For example, Mon45657 in List1 exists in List2 Row 9! If it does not exist in List2, then #N\/A is displayed.<\/p>\n

\"Match<\/p>\n

 <\/p>\n

Using either of the three ways mentioned in this article, you can easily compare two lists in Excel for matches<\/a>!<\/p>\n

Further Learning:<\/strong><\/p>\n