# Intersection of Two Columns in Excel (7 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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!

## 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? ### 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. ### 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. ### 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. 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.

## Related Articles Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  