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:
- 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.
- Select the ‘Top Row’ and ‘Left Column’ options and click OK.
- This will create named ranges for all the Sales Reps and all the Month.
- Now go to cell B16 and create a drop-down list for all the sales rep.
- Similarly, go to cell C15 and create a drop down list for all the months.
- 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:
7 thoughts on “What is Intersect Operator in Excel and How to Use it”
Very good book
Thanku
Excellent et s’avère extrêmement utile. Merci bcp
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?
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
Instead of pressing F9 what can I click on?
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)?
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?
Comments are closed.