While working in **Microsoft Excel **sometimes we need to find intersection of two columns. But sometimes it becomes difficult if you are not aware of the proper intersection operator or functions. Today in this article, I am sharing with you how to find intersection of two columns in Excel. Stay tuned!

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 7 Quick Methods to Find Intersection of Two Columns in Excel

In the following, I have described 7 simple and easy methods to find intersection of two columns in Excel.

Suppose we have a dataset of **Product A**, **Product B**, and **Product C** sales month-wise. Now we will find intersection between these sales columns with some simple tricks.

### 1. Combination of IF, ISERROR, and MATCH Functions to Find Intersection of Two Columns

With the combination of **IF**, **ISERROR**, and **MATCH functions **you can simply find the intersection of two or more columns. Follow the instructions below-

**Steps:**

- First, choose a
**cell**(**G5**) and apply the below formula down-

`=IF(ISERROR(MATCH(C5:C14,$D$5:$D$14,0)),"",C5:C14)`

Where,

- The
**MATCH function**will lookup for**values**in the**lookup array**(**$D$5:$D$14**). - The
**ISERROR function**will check whether the value is an**error**or**not**and return**TRUE**or**FALSE**. - The
**IF function**will provide the final output checking whether the given condition is met and returning the**value**if it’s**TRUE**.

- Next, press
**ENTER**. - As a result, you will find the intersection output in the new cell. Simple isn’t it?

**Read More: ****How to Find Intersection of Two Lists in Excel (3 Easy Methods)**

### 2. Using Space Between Column Ranges to Find Intersection of Two Columns

Sometimes you might face difficulties using formulas. For this, I have come up with an intersect operator. With this, you can easily find the intersection of two columns without any formula.

**Steps:**

- Simply, choose a
**cell**(**G5**) and write the below formula down-

`=C5:D14 D5:E14`

Where,

- We have used a
**single space**as an**intersection operator**between two column**ranges**.

- Gently, click the
**ENTER**key from the keyboard to get the result. - As you can see we have extracted the intersection output in a new column.

**Read More: ****Performing Intersection of Two Data Sets in Excel (4 Easy Ways)**

### 3. Utilizing Named Ranges to Find Intersection of Two Columns

You can also utilize the named ranges feature to find the intersection of two columns in Excel. Just go through the steps below-

**Step 1:**

- First, let’s start with defining names for the columns. To do so choose
**cells**(**C5:D14**) and choose “**Define Name**” from the “**Formulas**” option.

- Second, provide a name for the chosen column range in the “
**New Name**” window and press**OK**.

- Similarly, select
**cells**(**D5:E14**) and click “**Define Name**” from the “**Formulas**” tab.

- Therefore, write your desired name in the “
**Name**” section and hit**OK**.

- Again, choose row-wise
**cells**(**B7:E7**) and provide a name in the “**New Name**” window, and click**OK**.

**Step 2:**

- After finishing naming columns and rows we will apply the below formula in
**cell**(**G5**) to find the intersection output-

`=Sales_of_Product_AB:Sales_of_Product_BC March`

- Hence, hit
**ENTER**. - Finally, we have successfully extracted our intersection result from multiple columns within seconds.

**Read More: ****Intersection of Row and Column in Excel is Called a Cell**

### 4. Applying VLOOKUP Function to Find Intersection Value

The **VLOOKUP function **is one of the most powerful functions in Excel which is used to lookup for values from a column range of cells. Go through the steps below-

**Steps:**

- Start with choosing a
**cell**(**G5**) and putting the below formula-

`=VLOOKUP(C5,$D$5:$D$14,1,0)`

Where,

- The
**VLOOKUP function**will lookup for the given cell value in**cell**(**C5**) from a specific**column range**(**D5:D14**).

- Then press
**ENTER**. - In conclusion, the
**VLOOKUP**function will provide us with the intersection output in the chosen cell. Simple isn’t it?

### 5. INDEX, SMALL, IF, COUNTIF, MATCH, ROW, and ROWS Functions for Intersection of Two Columns

While working with a large dataset you might face problems with finding multiple intersection values. Well, you can use **INDEX**, **SMALL**, **IF**, **COUNTIF**, **MATCH**, **ROW**, and **ROWS functions **to find the intersection of two columns easily.

**Steps:**

- To begin with, select a
**cell**(**G5**) and write the below formula down-

`=INDEX($C$5:$C$14, SMALL(IF(COUNTIF($D$5:$D$14, $C$5:$C$14), MATCH(ROW($C$5:$C$14),ROW($C$5:$C$14)), ""), ROWS($A$1:A1)))`

- After that, hit the
**ENTER**key to get the result. - Finally, you will get your desired result in your hands.

**Formula Breakdown:**

- The
**COUNTIF function**will compare values within two**columns**(**D5:D14**) and (**C5:C14**) and return output as**1**or**0**. The output stands as**{1;0;0;0;0;0;0;0;0;0}**. **IF({1;0;0;0;0;0;0;0;0;0}, MATCH(ROW($C$5:$C$14),ROW($C$5:$C$14)), “”)**→ In this part, the**ROW****function**will provide the cells row number inside the argument. Then the**MATCH****function**will lookup for the position of the string. So, the result stands as-**{1;””;””;””;””;””;””;””;””;””}****SMALL(IF(COUNTIF($D$5:$D$14, $C$5:$C$14), MATCH(ROW($C$5:$C$14),ROW($C$5:$C$14)), “”), ROWS($A$1:A1))**→ In this section, we extracted a specific number from an array using the**SMALL****function**returning**1**.**INDEX($C$5:$C$14, SMALL(IF(COUNTIF($D$5:$D$14, $C$5:$C$14), MATCH(ROW($C$5:$C$14),ROW($C$5:$C$14)), “”), ROWS($A$1:A1)))**→ In this final part, the**INDEX****function**will provide the cell value from a particular location in**cell****range**(**C5:C14**). Thus the final result stands as**970**.

### 6. Combining FILTER and COUNTIF Functions

In this method, I have explained a dynamic **Excel 365 **formula to find the intersection of two columns. Simply, use the **COUNTIF**, and **FILTER functions **to count and then filter to get desired output.

**Steps:**

- In the same fashion, choose a
**cell**(**G5**) and apply the below formula-

`=FILTER($C$5:$C$14,COUNTIF($D$5:$D$14, $C$5:$C$14))`

Where,

- The
**COUNTIF function**will provide compared values from the given column ranges in the string. - The
**FILTER function**will return cell values from the given condition.

- Next, click
**ENTER**and get your precious intersected values in a new column.

### 7. Using FILTER, MMULT, EXACT, TRANSPOSE, and ROW Functions

In some situations, you can try the combination of **FILTER**, **MMULT**, **EXACT**, **TRANSPOSE**, and **ROW functions **to look for the intersection of multiple columns. Follow the instructions below-

**Steps:**

- Simply, choose a
**cell**(**G5**) and apply the below formula down-

`=FILTER($C$5:$C$14,MMULT(EXACT(C5:C14,TRANSPOSE(D5:D14))*1,ROW(C5:C14)^0))`

Where,

- The
**ROW function**will return the**row number**as**{5;6;7;8;9;10;11;12;13;14}**from the specified**cell range C5:C14**. - The
**TRANSPOSE function**will convert the**vertical**range of cells to a**horizontal** - The
**EXACT function**will perform a case-sensitive comparison between values. - The
**MMULT function**will return the matrix product of two arrays and finally, the**FILTER function**will provide the cell value from the given matrix condition.

- Just click
**ENTER**. - In conclusion, you will get the intersection output of two columns in Excel.

## Conclusion

In this article, I have tried to cover almost all the methods to find the intersection of two columns in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the __Exceldemy__** **team, are always responsive to your queries. Stay tuned and keep learning.