Using the Excel intersection operator

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.
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”:
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:
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:
Now, in order to retrieve the March Results for the East region we can simply use “=March East”:
Getting the Sum of the revenues from January-April for the West region is equally simple “=Sum(January:April West)”:
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)”:


Photo Credit: 1 brian via Compfight cc

I'd love to hear what you think

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

You are commenting using your 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 )

Connecting to %s