Aniruddah Alam
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.
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)
