In an excel worksheet, what is the intersection of a row and a column called?

What is Intersect Operator in Excel and How to Use it

• -- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

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.

Note: This invisible intersect operator get precedence over other operators. So if in this case, if you use =INDIRECT(B16) INDIRECT(C15)>5000, it will return TRUE or FALSE based on 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.

7 thoughts on “What is Intersect Operator in Excel and How to Use it”

1. Rimpisingla

   October 2020 at 9:10 pm

   Very good book
   Thanku

2. Mounir Mahmoudi

   March 2020 at 7:41 pm

   Excellent et s’avère extrêmement utile. Merci bcp

3. Alagappan Sockalingam

   March 2020 at 9:50 am

   How to use this operator in a Visual Foxpro Program with named ranges in excel? e.g., there are 2 ranges : RNAME AND TNAME. I want the intersect value. I entered as .range(tname rname).value. Error message recd. what is the correct coding?

4. Gregg Cannella

   August 2019 at 5:46 am

   this doesn’t work if you have spaces in the name (say “First” “Last”) because the named ranges put an Underscore where the space is, and the Indirect function doesn’t

5. Alberto

   April 2019 at 4:11 pm

   Instead of pressing F9 what can I click on?

6. Kenneth Barber

   September 2017 at 5:59 am

   Is there any way to find the set difference between 2 ranges (i.e. start with one range, and remove all cells from the other range)?

7. LB5000

   July 2017 at 3:26 pm

   From your above sales rep data table, is there any way to find the max value, which I believe is 9437, and then extrapolate backwards to find out that it was Rachael for the month of May?