Intersection of Two Columns in Excel (7 Easy Methods)

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!


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.

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

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

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

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.

Using Space Between Column Ranges to Find Intersection of Two Columns

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

Utilizing Named Ranges to Find Intersection of Two Columns

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

Utilizing Named Ranges to Find Intersection of Two Columns

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

Utilizing Named Ranges to Find Intersection of Two Columns

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

Utilizing Named Ranges to Find Intersection of Two Columns

  • 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).

Applying VLOOKUP Function to Find Intersection Value

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

Applying VLOOKUP Function to Find Intersection Value


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

Performing INDEX, SMALL, IF, COUNTIF, MATCH, ROW, and ROWS Functions to Find Intersection of Two Columns

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

Combining FILTER and COUNTIF Functions

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

Using FILTER, MMULT, EXACT, TRANSPOSE, and ROW Functions

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

excel intersection of two columns


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

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

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo