How to Perform an Intersection of Two Data Sets in Excel (4 Methods)

Dataset Overview

Let’s dive into the methods for finding the intersection of two data sets in Excel. We’ll use a sample dataset representing Month-wise Sales for Sales Reps. Here are the four methods:

excel intersection of two data sets

 


Method 1 – Finding Intersection of Data Sets in Two Columns

Steps

  • Create a new column labeled Intersection under Column E.

Finding Intersection of Data Sets in Two Columns

  • In cell E5, use the following formula:
=IF(ISERROR(MATCH(C5,$D$5:$D$16,0)),"",C5)

Formula Breakdown

  • MATCH(C5,$D$5:$D$16,0) →Returns the relative position of the lookup value.
    • 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)) → Checks for errors (e.g., if the value in cell C5 is not in the D5:D16 range).
  • 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)

 

  • Press ENTER.

  • Double-click the Fill Handle tool (the plus sign at the right-bottom corner of cell E5) to copy the formula to lower cells.

using Fill Handle to find intersection of two data sets

Finding Intersection of Data Sets in Two Columns in Excel

Duplicate values in two columns in Excel

  • The Intersection column will contain values available in both columns C and D.

Read More: Intersection of Two Columns in Excel


Method 2 – Getting Intersection of Row and Column in Excel

Steps

  • In cells C18 and C19, provide a random name for the Sales Rep and Month.

Getting Intersection of Row and Column in Excel 

  • To find Harry’s sales amount in March, select cell C20 and paste the formula:
=B6:E6 E4:E16
    • This formula calculates the intersection of row 6 (Harry) and column E (March).
  • Press ENTER.

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

  • Similarly, you can find sales amounts for the same Sales Rep in February and March using the formula:
=B6:E6 D4:E16
    • Adjust the row and column references as needed.

Read More: How to Use Intersection Operator in Excel


Method 3 – Applying Intersect Operator with Named Ranges

Steps

  • Highlight the entire dataset in the B4:E16 range.
  • Navigate to the Formulas tab.
  • Click on Create from Selection.

Applying Intersect Operator with Named Ranges

  • In the Create Names from Selection dialog box, check the boxes for Top row and Left column, then click OK.

  • The columns and rows have their respective names.
  • Enlist the names of Sales Reps and Months in cells C18 and C19, respectively, using Data Validation:
    • Go to cell C18.
    • Proceed to the Data tab.
    • Click on the Data Validation drop-down on the Data Tools group.
    • Select the Data Validation option from the list.

Data validation in Excel

  • In the Allow box, select List.
  • In the Source box, reference the B5:B16 range.
  • Click OK.

working on data validation dialog box in Excel

  • Repeat the same process for cell C19. Use the down arrow beside these two cells when you click on the cells.

  • Click on the arrow beside cell C18, and from the list, select John.

Selecting name from the list

  • Choose Mar in cell C19.

  • In cell C20, enter the following formula:
=INDIRECT(C18) INDIRECT(C19)
  • Press ENTER.

Applying Intersect Operator with Named Ranges in Excel


Method 4 – Employing VBA Code

Steps

  • Create a column with the heading Intersection under Column E (similar to Method 1).

Employing VBA Code

  • Go to the Developer tab and click on Visual Basic in the Code group.

  • In the Microsoft Visual Basic for Applications window, go to the Insert tab and select Module.

Inserting module in Excel

  • 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

  • Save the file as a macro-enabled workbook.
  • Select cells in the C5:C16 range (sales in Jan month).
  • Navigate to the Developer tab and click on Macros.

  • In the Macro dialog box:
    • Select the Intersection_Two_Columns macro in the Macro name box.
    • Click the Run button.

Running the Macro in Excel

  • The output will be the same as Method 1.

Employing VBA Code in Excel


Practice Section

To practice on your own, you’ll find a Practice section similar to the one below on each sheet to the right. Feel free to work through the exercises there.


Download Practice Workbook

You can download the practice workbook from here:


<< Go Back to 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

 

 

ExcelDemy
Logo