How to 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:

Excel find intersection of two column lists

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:

This represents the intersection between these two column lists.

Recall the formula that we used to find the intersection between the two column lists:

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

The following tutorials explain how to perform other common operations in Excel: