How to Use Intersection Operator in Excel (5 Handy Examples)

An intersection operator or operation is a very useful tool to determine the intersection between various columns and rows’ cell values. It can return multiple values in array format or return a single value depending upon the criteria using the Intersection operator in Excel.

Below we showed an example where we can get the intersected cell values between a column and a row. Where we not only get the intersection of values, we also get the summation of them.

What Is Intersection Operator in Excel?

The intersection operator in Excel is represented by a single space character and is used to return the common cells or range of cells between two or more ranges. The result of an intersection operation is a new range that contains only the cells that are present in all of the specified ranges.

So basically it’s just a space tool character placed in between ranges and column values.

The intersection operator can be used in conjunction with other Excel functions, such as SUM and COUNT, to extract specific data from complex datasets. Additionally, if you want to select the intersecting cells or range, you can highlight the ranges and press “Ctrl + Shift + Space” on your keyboard.

How to Use Intersection Operator in Excel: 5 Handy Examples

Below we showed 5 separate examples of using the Intersection operator in Excel. In order to avoid any kind of compatibility issues, try to use the Excel 365 edition instead.

1. Intersection Command Using Function Button in Excel

The function keyboard button is probably the easiest method to use the intersection operator or carry out the intersection operation. It just needs a Function F9 button press.

Steps:

• We can get the intersection of the values between two datasets using the Function Button F9.
• Users first have to enter the first level of rows, in our case the first range of rows is C5:E7.
• Then put a space after this by pressing the SpaceBar on the Keyboard.
• Then drag the cursor to select the range of cell B6:E6.
• Press Enter after this.
• After that, you can notice that the intersecting range between the two ranges mentioned above is now present in Cell C14.

2. Using Named Ranges to Determine Intersecting Ranges in Excel

• First of all, to implement this method, we need to create some named ranges first.
• For this, first, select the range of cell C5:C7 and then go to the Name Box.
• Then type Institute_1, which will rename the range of cell C5:C7 as Institute_1.

• Repeat this process two more times for the range of cells D5:D7 and E5:E7.
• Hence we managed to name the ranges C5:E7
• Now we will name the Range of cell B6:E6 as Physics.

• We can see all of the named ranges that we created on the Name Manage.
• After completion of the naming, we can now move forward to do the intersection of the ranges in Excel.
• Select cell C14 and then enter the following

• Then press the Function key F9.
• After that, you can notice that the intersecting range between the two ranges mentioned above is now present in Cell C14.

3. Using Intersection Command to Summation of Values

We obviously can use an intersection command to carry out summation operations in the intersected values.

Steps:

• For this, Select cell And start entering the following formula:

• After entering the formula, press the Function key F9.
• You will notice the summation of the intersecting ranges C6:E6 in cell C10.

4. Using Intersection Command to Get the Maximum and Minimum of Intersecting Cells

Similar to the previous example, we can use the intersection operator to get the maximum or the minimum value of the intersected values.

Steps:

• Select cell D10, and then enter the following formula:

• After entering the formula, press the F9 function key.
• After pressing the F9 key, you can see that the maximum value in the insection values is now present in the cell
• You can repeat the process and get the minimum value of the intersected cells.
• For this again enter the following formula in cell C12 and then press Function key F9

• After pressing the Function button F9, we can see that the minimum cell value among the intersected cells between the C5:E7 and B6:E6 is now showing.

5. Implementing VBA Macro to Extract Intersecting Values

We can use the VBA macro to extract intersecting values in Excel. You can open the developer tab, and you can follow this helper article.

Steps:

• Open the visual basic from the Developer tab.
• Then go to the Insert > Module.
• Then in the code editor window, paste the below code.

Sub ExtractIntersect()
    Dim colRange As Range
    Dim intersectRange As Range
    Dim intersectingCells As Range
    Dim cell As Range
    Dim intersectingValues() As Variant
    Dim i As Integer
    Set colRange = Application.InputBox("Select the column to check for intersecting values", "Select Column", Type:=8)
    Set intersectRange = Application.InputBox("Select the range to check for intersecting values", "Select Range", Type:=8)
    Set intersectingCells = Application.Intersect(colRange, intersectRange)
    ReDim intersectingValues(1 To intersectingCells.Cells.Count)
    i = 0 
    For Each cell In intersectingCells
        i = i + 1
        intersectingValues(i) = cell.Value
    Next cell
    If i > 0 Then
        MsgBox "The following values are intersecting between the selected column and range:" & vbCrLf & Join(intersectingValues, vbCrLf)
    Else
        MsgBox "There are no intersecting values between the selected column and range."
    End If
End Sub

Sub ExtractIntersect()
    Dim colRange As Range
    Dim intersectRange As Range
    Dim intersectingCells As Range
    Dim cell As Range
    Dim intersectingValues() As Variant
    Dim i As Integer

Set colRange = Application.InputBox("Select the column to check for intersecting values", "Select Column", Type:=8)
Set intersectRange = Application.InputBox("Select the range to check for intersecting values", "Select Range", Type:=8)

Set intersectingCells = Application.Intersect(colRange, intersectRange)

ReDim intersectingValues(1 To intersectingCells.Cells.Count)
i = 0 
For Each cell In intersectingCells
    i = i + 1
    intersectingValues(i) = cell.Value
Next cell

If i > 0 Then
    MsgBox "The following values are intersecting between the selected column and range:" & vbCrLf & Join(intersectingValues, vbCrLf)
Else
    MsgBox "There are no intersecting values between the selected column and range."
End If

Frequently Asked Question

• What is Union Operator in Excel?

In Excel, the union operator is used to combine two or more ranges into a single range. The union operator is represented by a comma (,) between two or more range references.

For example, if you want to combine the range A1:A5 with the range C1:C5, you would write it as “A1:A5, C1:C5” (without quotes) using the union operator.

Here’s an example of how to use the union operator in a formula or function:

• How do I find the intersection of two columns in Excel?

Normally there is no intersection between two columns, but you still can find an intersection value in between them(Same value in both columns) following the below method.

In order to find the intersection between column values Sales of Product A and Sales of Product B Enter the following formula in cell G5,

• After pressing Enter, we can see the insection value between two column values.

Things to Remember

You need to keep in mind the points given below while working with the Intersection operator in Excel.

• The ranges must be of the same dimension (i.e., they must have the same number of rows and columns) for the intersection operation to work.
• If there is no intersection between the specified ranges, the result will be an empty cell or range.
• You can use the intersection operator with more than two ranges.
• The intersection operator is case-insensitive, so you can use upper- or lowercase letters when specifying ranges.

Download Practice Workbook

You can download the Excel workbook from here.

Conclusion

In this article, we have shown how you can use the intersection operator in Excel. We have given a brief overview of this and then we proceed to give 5 separate use cases of using the Intersection operator. In that method, we have a keyboard shortcut with the original intersection operator “Space”, then we proceed to give an example of using various combinations of formulas to execute the intersection operator in Excel. Among all of those methods, the keyboard shortcut and the original space operator is the most useful one. We also showed how you can use the Intersection operator in Excel to carry out various arithmetic operations.

Related Articles

• Intersection of Row and Column in Excel is Called a Cell
• Performing Intersection of Two Data Sets in Excel
• Intersection of Two Columns in Excel
• How to Use Implicit Intersection Operator in Excel

<< Go Back to Excel Intersection | Excel Operators | Excel Formulas | Learn Excel