Hello everyone! Did you know…by deploying the COUNTIF function in your spreadsheet you can easily match two lists on a particular field.
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