Intersections

This is a Really Cool Trick that you probably have never run across! The concept is known as Range Intersection, and identifies the value in the cell that two ranges have in common.

The syntax is remarkably effortless: =Range1 Range2

You simply state the two ranges in the formula separated by a Space (Important Note: No commas, semicolons, etc, Just a Space).

Where using a Range Intersection becomes particularly powerful is when you use Named Ranges in your table. Using the following table for an example, I have named the columns in accordance with their Headers (Quarter1, Quarter2, etc) and the rows according to the State in Column A (California, Minnesota, etc).

The formula, =Minnesota Quarter2, was then put into cell B7 and the result, 1,420, was returned. This is easily verified by identifying the Intersecting Cell of the two ranges: C3.

Using the Range Intersection tool is an elegant way to select values in a Cross-Tab table. Just another way to accomplish a daily task with our favorite software. Cheers!