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.

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.

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

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

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

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

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.

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.

• Following this, press the ENTER key.

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

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.

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.

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.

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.

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

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.

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

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

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

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

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.

Conclusion

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