# How to Find Intersection of Two Lists in Excel (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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.

### 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.

### 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.

## 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.

## Related Articles #### Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  