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.

Here are our 2 Lists:

MATCH Two Lists

MATCH Two Lists

DOWNLOAD EXCEL WORKBOOK

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

=MATCH(

MATCH Two Lists

 

STEP 2: The MATCH arguments:

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

Lookup_array

What is the list you want to check against?

Select the entire List2.

MATCH Two Lists

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

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

MATCH Two Lists

Match_type

How specific is your matching?

We want an exact match so place in 0.

MATCH Two Lists

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

MATCH Two Lists

You now have all of 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.

MATCH Two Lists

 

How to MATCH Two Lists in Excel

Match - Two Lists

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Related Excel Tutorials