In this article, I will show how we can find **intersection of two lists** in **Excel**. While working in Excel, we may want to compare the elements of two lists and separate the common elements. If you are wondering how to deal with these kinds of situations, you have come to the right place. Here, I will show some very easy and simple ways to perform the task. So, let’s get started.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Useful Methods to Find Intersection of Two Lists in Excel**

In this section, we will demonstrate 3 effective methods to find common elements of two lists in Excel. For illustration purposes, I have taken a data set containing two lists of flowers where there are 3 of them which are in both lists (**Jasmine**, **Tulip**, and **Daffodils**)

Now, our target is to separate those common flowers from those 2 lists. We will achieve our task using the methods below one by one. Let’s look at the first method.

### 1. Combining FILTER and COUNTIF Functions to Find Intersection of Two Lists

In this method, we will use the combination of **FILTER **and **COUNTIF Functions** to find the common elements of both lists. To do this, follow the steps below.

**Steps:**

- First, go to cell
**E5**and write down the following formula.

`=FILTER(B5:B15,COUNTIF(C5:C15,B5:B15))`

- Now, hit the
**Enter**key and you will see the 3 intersecting/ common flowers. [If you don’t have the latest version of Excel, you may have to press**Ctrl+****Shift+Enter**key to get the results]

**🗝️ How Does the Formula Work?**

Here the **COUNTIF **function matches the** Flower List 2** elements with **Flower List 1** elements. Then the **FILTER **function returns an array containing the matched elements.

**Read More: How to Find Graph Intersection Point in Excel (5 Useful Methods)**

**Similar Readings**

**How to Use COUNTIF Function to Count Text from List in Excel****How to Make Credit Card Debt Reduction Calculator for Excel****VBA COUNTIF Function in Excel (6 Examples)****Use Excel COUNTIF That Does Not Contain Multiple Criteria****How to Apply COUNTIF Between Two Cell Values in Excel**

### 2. Finding Intersection of Two Lists by Combined Functions

In this method, we will use **ISERROR**, **IF **and **MATCH **functions to accomplish our target of finding common elements or** intersections of two lists** in Excel. Here, the results will not come in as organized manner as we saw in the 1st method. Nevertheless, it is pretty much useful to look into. So, let’s explore this method by following the steps below.

**Steps:**

- Firstly, go to cell
**E5**and write down the following formula.

`=IF(ISERROR(MATCH(C5,$B$5:$B$15,0)),"",C5)`

- Now, hit the
**Enter**button. Then, use the**Fill Handle**to autofill the rest of the cells till**E15**. You should see that the common flowers will appear just in the row position of them in Flower List 2.

Though the interesting or common items don’t come at a time, we can at least check which items are common in both of the lists by this method.

**🗝️ How Does the Formula Work?**

**MATCH(C5,$B$5:$B$15,0)**

Here, the **MATCH **function searches the **C5 **value in the **B5:B15** range. If the function finds the value, then it will give the returns the relative position of that item in the range B5:B15. Otherwise, it will result in an error.

**ISERROR(MATCH(C5,$B$5:$B$15,0)**

Here, **ISERROR **determines whether the **MATCH **function gives any error or not. If the **MATCH **function returns any error, **ISERROR **will return **TRUE**. Otherwise, it will return **FALSE**.

**IF(ISERROR(MATCH(C5,$B$5:$B$15,0)),””,C5)**

The IF Function will return nothing if it finds the returning value of **ISERROR **is true. Else, it will return the 3rd argument which in this case is **C5**.

**Read More: Intersection of Row and Column in Excel is Called a Cell**

### 3. Use of Intersection Operator to Find Intersection of Two Lists

In this method, we will learn about the built-in **Intersection **operator in Excel. You may be surprised that the built-in **intersection **operator in Excel is nothing but a **space **character. If you write two ranges in the formula bar with space, Excel will return the intersecting cells as results. To know more, follow the steps below.

**Steps:**

- Below, we have taken a data set containing some salesmen and their amount of sales in the months of January to June.

- From the table, we can see the list of sales in the month of May is in the range of
**C9:H9**. On the other hand, the list of the amount of sales for Jordan is in the range of**G5:G10.**

- Hence, the intersecting cell of those two lists will be
**G9**. Now, we can find the**intersection of two lists**by using the Intersection operator. To do that, write the ranges in the formula bar with a**space**in between like this below.

`=G5:G10 C9:H9`

- Now, if you click
**Enter**, you will see the intersecting cell(s) as a result.

In this way, we can find the **intersection of two lists** by using the Excel Intersection operator.

**Read More: How to Find Intersection of Two Curves in Excel (with Easy Steps)**

**Things to Remember**

- While using the
**Intersection**Operator, don’t forget to give**Space**in the formula bar. If you don’t have the latest version of Excel, you should press**Ctrl+ Shift +Enter**to get an array result.

**Conclusion**

That is the end of this article regarding how to find the** intersection of two lists in Excel**. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit **ExcelDemy** for more exciting articles on Excel.

## Related Articles

**How to Find Intersection of Two Trend Lines in Excel (3 Methods)****[Fixed!] Excel MATCH Function Not Working****Use COUNTIF for Non Contiguous Range in Excel****How to Use ISNUMBER & MATCH Function in Excel (2 Ways)****Performing Intersection of Two Data Sets in Excel (4 Easy Ways)****Use COUNTIF Function In Excel to Count Bold Cells****How to Interpolate Between Two Values in Excel (6 Ways)**