You can use the Excel COMPARE function to extract common values from two lists and get the result into a dynamic array.
Today’s tutorial will show you how to write a formula using the COMPARE function. Furthermore, you can learn another method to compare two lists.
How to extract common values from two lists
- Type =COMPARE(list1, list2)
- Press Enter
- The formula will return the common values
Example
Generic formula:
=COMPARE(range1, range2)In the example, we have two lists. The goal is to extract common values that exist in 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.
Formula:
=COMPARE(list1, list2)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. You can learn more about the function here.
Using the FILTER and COUNTIF function
Alternatively, you can combine the FILTER and COUNTIF functions to extract common values.
Formula:
=FILTER(list1,COUNTIF(list2,list1)
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 used 7 logical criteria, so the return array should contain 7 values. The FILTER function filters the first range (list1) using the output array of the COUNTIF function. If the value is greater than 0, the value will use; else not.
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.
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:
