While working in Microsoft Excel sometimes we need to find the 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 the intersection of two columns in Excel. Stay tuned!

## Intersection of Two Columns in Excel: 7 Easy Methods

In the following, I have described 7 simple and easy methods to find the 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 the 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 look 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: **Performing Intersection of Two Data Sets in Excel

### 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: **Intersection of Row and Column in Excel is Called a Cell

### 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**) 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: **How to Use Intersection Operator in Excel

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

**The VLOOKUP function **is one of the most powerful functions in Excel which is used to look 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 look 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 look 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 the 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 array. - 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.

**Download Practice Workbook**

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

## 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. Stay tuned and keep learning.

## Related Article

**<< Go Back to Excel Intersection | Excel Operators | Excel Formulas | Learn Excel**