Table of Contents
The method to find the intersection of two column lists in Excel involves using the “IF” and “COUNTIF” functions. First, the “IF” function is used to compare the values in each column and return a result of “TRUE” or “FALSE”. Then, the “COUNTIF” function is used to count the number of “TRUE” results. The intersection of the two lists is where the “COUNTIF” function returns a value of 2, indicating that the same value appears in both columns. This method allows for easy identification of common values between the two lists.
Find Intersection of Two Column Lists in Excel
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: