What is Intersect Operator in Excel and How to Use it

Watch Video – Using Intersect Operator in Excel

Intersect Operator in Excel can be used to find the intersecting value(s) of two lists/ranges. This an unusual operator as it is represented by a space character (yes that’s right).

If you use a space character in between two ranges, then it becomes the Intersect operator in Excel.

Intersect Operator in Excel

You can use Intersect Operator in Excel to find:

• The intersection of a single row and column.
• The intersection of multiple rows and columns.
• The intersection of Named Ranges.

Intersection of a Single Row and Column

Suppose there is a data set as shown below:

Now if you use =C2:C13 B5:D5 [Note there is a single space in between the ranges, which is also our intersect operator in Excel], it will return 523 (the value in cell C5), which is the intersection of these 2 ranges.

Intersection of a Multiple Rows and Columns

You can also use the same technique to find the intersection of ranges that spans more than one row or column. For example, with the same data set as shown above, you can get the intersection of Product 1 and Product 2 in April.

Here is the formula that can do that: =B2:C13 B5:D5

Note that the result of this formula would display a Value error, however, when you select the formula and press F9, it will show the result as {649,523}. This formula returns an array of the intersection values. You can use this within formulas, such as SUM (to get the total of the intersection values) or MAX (to get the maximum of the intersection values).

Intersection of Named Ranges

You can also use named ranges to find the intersection using the Intersect Operator in Excel.

Here is an example where I have named the Product 1 values as Prdt1, Product 2 values as Prdt2 and April Values as Apr.

Now you can use the formula =Prdt1 Apr to get the intersection of these 2 ranges. Similarly, you can use =Prdt1:Prdt2 Apr to get the intersection of Product 1, Product 2 and April.

A Practical Example of Using Intersect Operator in Excel

Here is a situation where this trick might come in handy. I have a data-set of Sales Rep and the sales they made in each month in 2012.

I have also created a drop-down list with Sales Rep Name in one cell and Month name in another, and I want to extract the sales that the Rep did in that month.

Something as shown below:

How to create this:

1. Select the entire data set (B3:N13) and press Control + Shift + F3 to create named ranges (it can also be done through Formula –> Defined Names –> Create from Selection). This will open a ‘Create Names from Selection’  dialogue box.
2. Select the ‘Top Row’ and ‘Left Column’ options and click OK.
3. This will create named ranges for all the Sales Reps and all the Month.
4. Now go to cell B16 and create a drop-down list for all the sales rep.
5. Similarly, go to cell C15 and create a drop down list for all the months.
6. Now in cell C16, use the following formula =INDIRECT(B16) INDIRECT(C15) 

How does it work?

Notice that there is a space in between the two INDIRECT formulas.

The INDIRECT function returns the range for the named ranges – Sales rep and the Month, and the space between them works as an intersect operator and returns the intersecting value.

You May Also Like the Following Excel Tutorials:

• How to Use Excel Freeze Panes to Handle Large Data Sets.
• How to Lock Cells in Excel.
• How to Calculate Compound Annual Growth Rate (CAGR) in Excel.