Revisiting Intersections

As you may have gathered, I have a soft-spot in my heart (some might say my mind…) for obscure Excel tools and techniques. The Intersect Operator is most certainly one of those obscure tools, and it can be extremely helpful if you know how to use it.  

Although there are a great many ways to gather information from Excel databases and tables, some can involve rather cumbersome formulas that can take a significant amount of time to construct and verify.  On the other hand, the Intersect Operator, is not only Powerful and Versatile, it is also quite Effortless to use (“Effortless” is always a good thing…)!

This handy tool uses the vertical and horizontal ranges in a cross-tab Table or Database, and finds the value at the Intersection (ergo, the Intersect Operator…).  The syntax (Special Note: Be sure to use No brackets or commas…) is simply:

= (RangeName1 RangeName2)

For the Ranges, you can use the generic names such as =(C2:C42 A10:K10).  This certainly gets the job done, but it is advantageous in many cases to use Named Ranges, as they are typically much more effective.

As you probably know, you can very quickly name all of the ranges in your database by selecting all cells (use Ctrl + A), and the click Ctrl + Shift + F3. This will bring up the Create Names dialogue box as shown below.  Just click OK, and Presto - Named Ranges!

 

Example: Once your ranges are Named, you can then find a value with the modest function (I’m using Cell E5 for the example):

=(Los_Angeles Quarter3)  Note: Be sure to include the space between the column title and the row title.

 

There are many, many ways of obtaining this information in Excel, but the Intersect Operator is certainly an important technique to keep in mind.  It may be an “Effortless” way to get the information you are looking for.