Using the Excel intersection operator

tree
One of the lesser-known features of Excel is the intersection operator which can be used to simplify look-up operations. An intersection is the overlap of two or more cell ranges within excel. For instance:
In the screenshot below the ranges C1:C5 and B3:D3 (Cell ranges in Excel are written by using the range operator “:”) overlap in the cell C3.
intersection1
The intersection operator ” ” (a space) can be used to find the intersection of ranges. To find the intersection of the two ranges one can just use the following formula “=C1:C5 B3:D3”:
intersection2
Combining the intersection operator with named ranges yields to pretty intuitive look-ups in Excel. Let’s take up another example using monthly revenue data by region:
intersection3
Highlighting the table and pressing CTRL+SHIFT+F3 will bring up the “Create Names from Selection” dialog. We can go with the defaults (Top row, Left column) in order to create named ranges for each column and row within the table based on their labels:
intersection4
Now, in order to retrieve the March Results for the East region we can simply use “=March East”:
intersection5
Getting the Sum of the revenues from January-April for the West region is equally simple “=Sum(January:April West)”:
intersection6
Even non-consecutive ranges can be easily referred to. Pulling up the Sum of revenues for the month of January, March, and May for the South region is as easy as typing “=Sum((January,March,May) South)”:
intersection7

shareThoughts


Photo Credit: 1 brian via Compfight cc

Advertisements

I'd love to hear what you think

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s