Table of Contents
The intersection of two column lists in Excel can be found by using a combination of the IF and COUNTIF functions. The IF function is used to compare the two lists and return the values common to both lists, while the COUNTIF function is used to identify the number of times each value appears in the two lists. The IF and COUNTIF functions are then combined into a single formula to determine the intersection of the two lists. The resulting list will contain all of the values that are common to both lists.
You can use the following formula to find the intersection of two column lists in Excel:
=IF(ISERROR(MATCH(A2,$B$2:$B$11,0)),"",A2)
This particular example finds all of the common values between the range A2:A11 and B2:B11.
The following example shows how to use this formula in practice.
Example: Find Intersection of Two Column Lists in Excel
Suppose we have the following two lists of basketball team names in Excel:
Suppose we would like to find the intersection between these two lists, i.e. find the team names that appear in both lists.
To do so, we can type the following formula into cell D2:
=IF(ISERROR(MATCH(A2,$B$2:$B$11,0)),"",A2)
We can then click and drag this formula down to each remaining cell in column D:
Column D now only displays the team names that appear in both lists.
For example, we can see that the following team names appear in both lists:
- Mavs
- Rockets
- Kings
- Warriors
- Blazers
This represents the intersection between these two column lists.
How This Formula Works
=IF(ISERROR(MATCH(A2,$B$2:$B$11,0)),"",A2)
First, this formula uses the MATCH function to return the relative position of the team name in cell A2 in the range B2:B11.
We then use the ISERROR function to return a blank value if the team name in cell A2 cannot be found in the range B2:B11.
Otherwise, we simply return the team name from cell A2.
We repeat this process for each cell in column A and the end result is that the formula only returns the team names that appear in both lists.