Pinterest Pixel

Match Two Lists With The MATCH Function

You can quickly compare two lists in Excel for matches using the MATCH function, IF function, or... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Match Two Lists With The MATCH Function | MyExcelOnline Match Two Lists With The MATCH Function | MyExcelOnline

You can quickly compare two lists in Excel for matches using the MATCH function, IF function, or highlighting row difference.

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

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

Let’s look at each method one-by-one!

 

Don’t forget to download this Excel Workbook to follow along and compare two lists in Excel for matches:

download excel workbookMatch-Two-Lists.xlsx

 

Highlight Row Difference

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

In the data below, you have two lists in Column A and Column B respectively.

Follow the steps below to highlight row difference:

STEP 1: Select both the columns.

Match Two Lists With The MATCH Function

STEP 2: Go to Home > Find & Select > Go To Special or simply press keys Ctrl + G and Select Special to open the Go To Special dialog box.

Match Two Lists With The MATCH Function

STEP 3: Select Row Difference and Click OK.

See also  How To Use INDEX-MATCH Formula

Match Two Lists With The MATCH Function

And, Voila!

Match Two Lists With The MATCH Function

All the values in Stock List 2 that do not match with the corresponding value in Stock List 1 have been highlighted.

 

STEP 4: You can mark these cells with color as well. Go to Home > Font Color > Select Red.

Match Two Lists With The MATCH Function

Match Two Lists With The MATCH Function

This will permanently highlight the cells in red font color for future reference.

 

Compare Row using IF function

You can use the IF Function 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.

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

Let’s see how we can compare two lists in Excel for matches using IF Function:

STEP 1: We need to enter the IF function in a blank cell.

=IF(

Match Two Lists With The MATCH Function

STEP 2: Enter the first argument for the IF function – Logical_Test

What is your condition?

The value in cell D12 is equal to the value in cell C12.

=IF(D12=C12,

Match Two Lists With The MATCH Function

 

STEP 3: Enter the second argument for the IF function – Value_if_true

What value should be displayed if the condition is true?

See also  SUBSTITUTE Function - Replace Characters in Excel!

The text displayed should be Match if D12 is equal to C12.

=IF(D12=C12,“Match”,

Match Two Lists With The MATCH Function

 

STEP 4: Enter the third argument for the IF function – Value_if_false

What value should be displayed if the condition is false?

The text displayed should be Not a Match if D12 is not equal to C12.

=IF(D12=C12,”Match”,“Not a Match”)

Match Two Lists With The MATCH Function

 

STEP 5: Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

Match Two Lists With The MATCH Function

 

Compare List using Match Function

Before we understand how to compare two lists in Excel for matches, let’s first go through the basics of what the MATCH function Excel does.

 

What does it do?

It returns the position of an item in a range.

Formula breakdown:

=MATCH(lookup_valuelookup_array[match_type])

What it means:

=MATCH(lookup this valuefrom this list or range of cellsreturn me the Exact Match).


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

Well, I have!

With the MATCH function, you can verify if a cell´s item in List1 exists in List2.

The function will return the row position of that item in List2 hence confirming that it exists.  If you get a #N/A it means that the cell´s item does not exist in List2.

You can then go ahead and filter your List1 with either the values returned or the #N/As.

See also  Getting the length of text with Excel's LEN Formula

Here are our 2 Lists:

Match Two Lists With The MATCH Function

Match Two Lists With The MATCH Function

STEP 1: We need to enter the MATCH function in a blank cell:

=MATCH(

Match Two Lists With The MATCH Function

 

STEP 2: Enter the first argument for the MATCH function – Lookup_value

What is the value you want to check?

Select the cell containing the List1 value, as this is what we want to check against List2.

=MATCH(C12,

Match Two Lists With The MATCH Function

 

STEP 3: Enter the second argument for the MATCH function – Lookup_array

What is the list you want to check against?

Select the entire List2.

Match Two Lists With The MATCH Function

And ensure to press F4 to make it an absolute reference.

=MATCH(C12, list2!$C$12:$C:21,

Match Two Lists With The MATCH Function

 

STEP 4: Enter the third argument for the MATCH function – Match_type

How specific is your matching? We want an exact match so place in 0.

=MATCH(C12, list2!$C$12:$C:21, 0)

 

Match Two Lists With The MATCH Function

 

STEP 5: Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

Match Two Lists With The MATCH Function

 

You now have all of the results!

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.

See also  INDEX-MATCH Maximum Sales

Match Two Lists With The MATCH Function

 

Using either of the three ways mentioned in this article, you can easily compare two lists in Excel for matches!

Further Learning:

If you like this Excel tip, please share it
Match Two Lists With The MATCH Function | MyExcelOnline Match Two Lists With The MATCH Function | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...