Intersections

The Intersect Operator is yet another somewhat obscure, but highly useful Excel tools.  Many of the ways to glean information from Excel tables involve cumbersome formulas that can take an Excel guru a considerable amount of time out of his or her busy day.  The Intersect Operator, is not only Powerful and Versatile, it is also quite Simple to use!

The function of this handy tool is to use the vertical and horizontal ranges in a cross-tab Table or Database, and find 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).  Whereas this gets the job done and may be advantageous in limited circumstances, using Named Ranges is much more effective.

As a reminder, 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 Bamm!  Named Ranges!

Once you have your ranges Named, you can then (as with the example below where we are finding the 3^rd Quarter Sales for Los Angeles) find a value with the modest function (in Cell E5 in our example):

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

 

There are, of course, other ways of obtaining this information in Excel, but the Intersect Operator is certainly a worthy addition to any Excel user’s tool belt.