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: