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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

## 4 Methods to Find Intersection of Two Data Sets in Excel

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.

### 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.
`=IF(ISERROR(MATCH(C5,\$D\$5:\$D\$16,0)),"",C5)`

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. 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: How to Find Intersection of Two Lists in Excel (3 Easy Methods)

### 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.
=B6:E6 E4:E16
• 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.

=B6:E6 D4:E16 Read More: How to Find Intersection of Two Trend Lines in Excel (3 Methods)

### 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. • Afterwards, select cell C20 and put down the following formula.
`=INDIRECT(C18) INDIRECT(C19)`

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 Find Intersection of Two Curves in Excel (with Easy Steps)

### 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. ## Related Articles Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

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