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**

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.

**Additional Resources**

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

How to Count Duplicates in Excel

How to Count Frequency of Text in Excel

How to Calculate Average If Cell Contains Text in Excel