Skip to content

Find Shared Values Between Two Lists

In this article, you will learn how to solve the following problem!

You have two lists of data within your spreadsheet.

You know these lists have some values in common, but you don’t know which ones. However, you want to write a formula to find out.

Here is how you can do it!

The Formula to Find Shared Values

You can use the following formula to find the shared values between two lists.

=FILTER(ListOne, COUNTIF(ListTwo, ListOne))

This formula uses both the FILTER and COUNTIF functions.

It is important to note that the FILTER function is only available in the Office 365 version of Excel. Older versions of Excel do not have access to the FILTER function.

How the Formula Works

Here is how the formula works.

The COUNTIF function calculates first and counts how many times each color in the first list appears in the second list. The COUNTIF function then returns an array of values to the FILTER function like this.

=FILTER(B3:B12, 0;1;1;0;1;1;0;0;0;1)

The FILTER function then looks at how many times each color from the first list appeared in list two.

After that, the FILTER function returns all color values that were counted one or more times. As a result, you end up with all the shared values between the two lists.

See It In Action

This Post Has One Comment

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top