Excel tutorials

If we have two or more lists in Excel – it is sometimes of value to extract the common values between them – values that are both in List A AND List B.

Here we are going to do this using the =FILTER() and =COUNTIF() functions.

Lets enter some data in column A and B.

Lets do this in steps – first we can use the =COUNTIF() function to count the number of fruits in the second list that are equal to the fruit in the first list.

Enter this formula in C2:

=COUNTIF(B2:B7;A2:A7)

If this return 0 – the fruit in List 1 is not in the second list.

Now we can use the =FILTER() function. The first argument to this function will be the first list above. The second argument is the result from the =COUNTIF() function above.

We can enter the following forumla in D2:

If a value is greater than 0 from the =COUNTIF() function, the =FILTER() function includes it in the result