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

Get FREE Advanced Excel Exercises with Solutions!

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
`=Institute_1:Institute_2 Physics`
• 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:

`=SUM(C5:E7 B6:E6)`

• 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:

`=MAX(C5:E7 B6:E6)`

• 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 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

`=MIN(C5:E7 B6:E6)`

• 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``````

VBA Code Breakdown

``````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``````
• This declares several variables that will be used throughout the code. Specifically, it sets up a Range variable for the column to check (colRange), a Range variable for the range to check (intersectRange), a Range variable for the cells that intersect (intersectingCells), a Range variable for each individual cell (cell), an array variable to store the intersecting values (intersectingValues), and a counter variable (i).
``````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)``````
• This prompts the user to select the column to check and the range to check using an input box. The Type:=8 parameter specifies that the user should select a range of cells.
``Set intersectingCells = Application.Intersect(colRange, intersectRange)``
• This checks for the intersection between the two ranges selected by the user and stores the result in the intersectingCells variable.
``````ReDim intersectingValues(1 To intersectingCells.Cells.Count)
i = 0
For Each cell In intersectingCells
i = i + 1
intersectingValues(i) = cell.Value
Next cell``````
• This loops through each cell in the intersectingCells range and adds the cell’s value to the intersectingValues array. The ReDim statement resizes the array to accommodate the number of intersecting cells.
``````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``````
• Finally, this displays a message box to the user that lists the intersecting values between the selected column and range, separated by a new line. If there are no intersecting values, it displays a message indicating so.
• After entering the code, Click on the Run
• After clicking Run, you will see an inputbox asking for the first range of cells. We selected D5:D7.
• Then there will be another inputbox asking for the second range of cells. We selected B6:E6.
• Then you will see that there is a message box showing the intersected value in between the selected ranges.

Read More: How to Use VBA Intersect Method in Excel

• 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:

`=SUM(A1:A5,C1:C5)`
• 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,

`=FILTER(\$C\$5:\$C\$14,MMULT(EXACT(C5:C14,TRANSPOSE(D5:D14))*1,ROW(C5:C14)^0))` • 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.

## 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 Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

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