Extract common values from two lists

Extract-common-values-from-two-lists-Excel

To extract common values from two lists in Excel, you can use a formula based on the FILTER function and the COUNTIF function.

Today’s tutorial will show you how to write a formula using the FILTER and COUNTIF functions. Furthermore, you will learn how to use the COMPARE function to compare two lists and extract the common records.

How to extract common values from two lists

Steps to extract common values from two lists in Excel:

  1. Open Excel
  2. Type =FILTER(list1,COUNTIF(list2,list1)
  3. Press Enter
  4. The formula will return the common values

Example

In the example, we have two lists: B3:B9 and C3:C9. You can write a simple formula and combine the FILTER and COUNTIF functions to extract common values.

Formula:

=FILTER(list1,COUNTIF(list2,list1)

Result:

Workaround-with-FILTER-function

Explanation:

Evaluate the formula from the inside out. The FILTER function’s first argument (array) is list1, which contains all values. To create an array for the “include” argument of the FILTER function, use the following formula:

  • =COUNTIF(list2, list1)
  • = {1, 2, 0, 1, 0, 1, 0}

We apply 7 logical criteria, so the result array includes 7 values. The FILTER function then screens list1 based on the output array from COUNTIF. If a value is greater than 0, FILTER includes it in the result; otherwise, it excludes it.

Formula:

=FILTER(list1, {1,2,0,1,0,1,0})

The formula will return an array that contains the common values and spills into the range D3:D6.

Using the COMPARE function

You can use the Excel COMPARE function to extract common values from two lists and get the result into a dynamic array.

COMPARE is a user-defined function; Excel does not contain it by default. However, you can improve the built-in function library by installing the free function add-in, DataFX.

Here is the generic formula:

=COMPARE(range1, range2)

In the example, we have two lists. The goal is to extract common values from both lists. So, first, create two named ranges, select the B3:B9 range, and add a name, for example, list1. Next, select the range C3:C9 and add list2 as a name using the name box.

Extract-common-values-from-two-lists-Excel

The result looks great; here are the common values: “Orange, Kiwi, Apple, Mango.”

Additional resources

We have a definitive guide if you want to compare two columns in Excel for matches and differences. If you want to take a closer look at how powerful can be a custom function, check the following resources: