Performing Intersection of Two Data Sets in Excel (4 Easy Ways)

While working in Excel, sometimes we face situations where there are duplicate values in different columns or we opt to find the value of intersecting cells in a particular row and column. In order to achieve these, we can apply numerous methods. In this article, we’ll demonstrate four easy and quick methods to find the intersection of two data sets in Excel. So, let’s go through the entire article to understand the procedures properly.

Intersection of Two Data Sets in Excel: 4 Methods

For ease of understanding, we are going to use the Month-wise Sales report of a particular organization. This dataset contains the names of Sales Reps and their corresponding sales amounts for Jan, Feb, and Mar in columns B, C, D, and E respectively.

excel intersection of two data sets

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll show 4 different methods to find the intersection of two data sets using the above data table in Excel. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Finding Intersection of Data Sets in Two Columns

In our first method, we’ll find the intersection of two contiguous columns. Here, with the word intersection, we mean that some values are duplicates in these two columns. Let’s find them using a formula combining multiple functions.

📌 Steps:

  • At the very beginning, create a new column with the heading Intersection under Column E.

Finding Intersection of Data Sets in Two Columns

  • Then, go to cell E5 and write down the following formula.

Formula Breakdown

  • MATCH(C5,$D$5:$D$16,0) The MATCH function returns the relative position of the lookup_value. $ sign is used for absolute reference.
    • Output#N/A (because the value of cell C5 is not available in the D5:D16 range.)
  • ISERROR(MATCH(C5,$D$5:$D$16,0)) The ISERROR function returns TRUE if it finds any type of error in the value. ISERROR(MATCH(C5,$D$5:$D$16,0)) becomes ISERROR(#N/A).
    • OutputTRUE
  • IF(ISERROR(MATCH(C5,$D$5:$D$16,0)),””,C5) becomes IF(TRUE,””,C5). IF function applies a logical concept.
    • Output →   (blank space)
  • After that, press ENTER.

  • Now, bring the cursor to the right-bottom corner of cell E5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • At this moment, double-click on it.

using Fill Handle to find intersection of two data sets

Immediately, it copies the formula to the lower cells and gives outputs to the remaining cells as well.

Finding Intersection of Data Sets in Two Columns in Excel

To understand the output, look at the following image carefully.

Duplicate values in two columns in Excel

Notice that, these three values in the Intersection column are available in both columns C and D.

Read More: Intersection of Two Columns in Excel

2. Getting Intersection of Row and Column in Excel

In the previous method, we applied the approach in two different columns. Now, we’ll get the intersection of row and column in Excel. So, let’s follow the steps below.

📌 Steps:

  • First of all, give a random name of the Sales Rep and Month in cells C18 and C19.

Getting Intersection of Row and Column in Excel 

Actually, what we are trying to do is to find the sales amount of Harry in Mar month.

  • Now, select cell C20 and paste the formula below.
=B6:E6 E4:E16
  • Following this, press the ENTER key.

working formula to find intersection value of two data sets in Excel

Similarly, you can get the sales amount for the same sales rep of Feb and Mar months. The formula is like the following.

=B6:E6 D4:E16

Read More: How to Use Intersection Operator in Excel

3. Applying Intersect Operator with Named Ranges

In this method, we’ll do something like the previous one. But the difference is that here we’ll use named ranges. Without further delay, let’s dive in.

📌 Steps:

  • First, highlight the whole dataset in the B4:E16 range.
  • Secondly, navigate to the Formulas tab.
  • Thirdly, click on Create from Selection.

Applying Intersect Operator with Named Ranges

Immediately, the Create Names from Selection dialog box appears before us.

  • Here, check the boxes of the Top row and Left column and click OK.

Now, the columns and rows have their respective names.

Forthwith, we’ll enlist the names of Sales Reps and Months in cells C18 and C19 correspondingly. For this, we’ll get help from the Data Validation feature.

  • At this time, go to cell C18.
  • Then, proceed to the Data tab.
  • After that, click on the Data Validation drop-down on the Data Tools group of commands.
  • Later, select the Data Validation option from the list.

Data validation in Excel

Suddenly, you can find the Data Validation dialog box, where the Settings tab will automatically open.

  • In the Allow box, select List.
  • In the Source box, give the reference of the B5:B16 range.
  • As usual, click OK.

working on data validation dialog box in Excel

Correspondingly, do the same for cell C19. You can find a down arrow beside you whenever you click on these two cells.

  • Here and now, click on the arrow beside cell C18, and from the list, select John.

Selecting name from the list

  • Alike, choose Mar in cell C19.

  • Afterward, select cell C20 and put down the following formula.

The INDIRECT function returns the cell reference of the argument value.

  • Also, hit ENTER.

Applying Intersect Operator with Named Ranges in Excel

That’s how you can easily create a nice working worksheet.

Read More: How to Use Implicit Intersection Operator in Excel

4. Employing VBA Code

Moreover, you can automate the entire approach of Method 1 with the help of VBA code. If you are interested in learning about VBA scripting, then go through this section.

📌 Steps:

  • Firstly, construct a column with the heading Intersection under Column E like in Method 1.

Employing VBA Code

  • Secondly, advance to the Developer tab and click on Visual Basic in the Code group.

Suddenly, the Microsoft Visual Basic for Applications window opens.

  • Thirdly, go to the Insert tab.
  • Then, click on Module from the options.

Inserting module in Excel

Instantly, Excel will insert a new module on the right of the display.

  • After that, paste the following code into the module.
Sub Intersection_Two_Columns()
Dim Intrsctn As Variant, a As Variant, b As Variant
Set Intrsctn = Range("D5:D16")
For Each a In Selection
For Each b In Intrsctn
If a = b Then a.Offset(0, 2) = a
Next b
Next a
End Sub

VBA code to find intersection of two data sets in Excel

  • Next, save the file as a macro-enabled workbook.
  • In this place, select cells in the C5:C16 range which are the sales in Jan month.
  • Later, navigate to the Developer tab and click on Macros.

In the Macro dialog box,

  • Firstly, select the Intersection_Two_Columns macro in the Macro name box.
  • Then, click on the Run button.

Running the Macro in Excel

As a result, it’ll show the same output as Method 1.

Employing VBA Code in Excel

Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it yourself.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice.


This article explains how to find the intersection of two data sets in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

<< Go Back to Excel Intersection | Excel OperatorsExcel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF