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

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

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

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

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

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

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


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 OperatorsExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo