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.

**Table of Contents**hide

## Download Practice Workbook

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

**and their corresponding sales amounts for**

*Sales Reps***,**

*Jan***, and**

*Feb***in columns**

*Mar***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
under*Intersection***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)**.**Output**→**TRUE**

**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
and*Sales Rep*in cells*Month***C18**and**C19**.

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

**month.**

*Mar*- 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

**months. The formula is like the following.**

*Mar***=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
under*Intersection***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 inmonth.*Jan* - 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.

## 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. Please visit our website, **Exceldemy**, a one-stop Excel solution provider, to explore more.