The Intersect Operator Revisited

The Intersect Operator is one of my favorite obscure Excel tools. Although relatively obscure, it is highly useful, as there are many ways it can be used to garner information from Excel tables without using awkward formulas that can take time and patience.  Happily, the Intersect Operator, is not only Utile and Flexible, it is also quite Easy to use (which is always a good thing…).

The syntax is simply: = RangeName1 RangeName2 (Please Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =C2:C34 B10:H10), but that is a bit awkward (and who needs that!)

As you may know, I am a huge proponent of creating Named Ranges in your Excel workbooks. This can be Easily Done by highlighting your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. This will bring up a default dialogue box as shown below (Just click OK, and Bamm, Named Ranges!).

Once you have your ranges Named, you can then (per the example below) find a value with the rudimentary function: =East Quarter3

But, Wait, That’s Not All! You can combine the useful Intersect Operator with statistical functions to find even more information! For instance, if you wanted to find the overall average quarterly sales for the “East” region, you could use: =AVERAGE((East Quarter1):(East Quarter4))

Are there many other ways of doing this in Excel?  Of course, but any good Excel Guru should have the Intersect Operator in their bag of tricks. (You can never have too many tricks…).