Home » Blog » Match two lists

Match two lists

Hello everyone! Did you know…by deploying the COUNTIF function in your spreadsheet you can easily match two lists on a particular field.

click to INTERACT

Choose the field for match two lists

In this example I am going to match on Product Code field column.

List 1

Our product codes go down column A from cell A2 to A15 and we enter:

PRD123 through PRD136

in consecutive order.

List 2

Our second list lists four codes in column F in cells F2 to F5 and they are:

PRD127

PRD130

PRD135

PRD137

Our List 1 formula

In cell B2 in list 1 we enter the formula:

=COUNTIF($F$2:$F$5,A2)

What the formula does

So we are checking all cells in the second list comprising of the four codes, to count how many times they match what we have in cell A2 which in this case is PRD123. This product code does not exist in list 2 so we get a count of 0. Copying this formula down we get counts of 0 if the particular code is not present in list 2 and a count of greater than or equal to 1 if it is present.

Similar List 2 formula

It’s the same type of formula in cell G2:

=COUNTIF($A$2:$A$15,F2)

After copying down in column G we find all four have a count of 1 except for code “PRD137” which has a count of 0 as it does not exist in our list 1.

Final results for match two lists

So at the end we can group each list into those records or rows that appear in both lists and those which appear in its own list only and so I think knowing the COUNTIF function can really help us in our daily spreadsheet work!

Get spreadsheet


click to INTERACT

Leave a Reply

Your email address will not be published. Required fields are marked *