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.

**Table of Contents**hide

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

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

`=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.

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