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:
Method 1 – Finding Intersection of Data Sets in Two Columns
Steps
- Create a new column labeled Intersection under Column E.
- 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).
- 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)
- 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.
- 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.
- To find Harry’s sales amount in March, select cell C20 and paste the formula:
-
- This formula calculates the intersection of row 6 (Harry) and column E (March).
- Press ENTER.
- Similarly, you can find sales amounts for the same Sales Rep in February and March using the formula:
-
- 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.
- 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.
- In the Allow box, select List.
- In the Source box, reference the B5:B16 range.
- Click OK.
- 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.
- Choose Mar in cell C19.
- In cell C20, enter the following formula:
=INDIRECT(C18) INDIRECT(C19)
-
- The INDIRECT function returns the cell reference of the argument value.
- Press ENTER.
Method 4 – Employing VBA Code
Steps
- Create a column with the heading Intersection under Column E (similar to Method 1).
- 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.
- 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
- 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.
- The output will be the same as Method 1.
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 Operators | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!