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