MrExcel.com - Your One Stop for Excel Tips & Solutions

Why Use the Intersection Operator?

January 27, 2022 - by

Why Use the Intersection Operator?

Problem: What is the purpose of the intersection operator?

Strategy: The intersection is the most obscure of the operators. Let’s run through some examples of other operators first.

The simplest reference is when you point to a single cell.

Products in A2:A8. Months in B1:I1. Numbers in the middle of the table. A formula of =SUM(B3) will return the value from B2.
Figure 296. Pointing to a single cell.

If you sum two cells and separate those cells with a comma, then Excel will add up the two individual cells. Below, the formula is adding B3 and I3.

A formula of =SUM(B3,I3) returns the Union of B2 and I3. In other words, you will add B3 and I3. The comma is called the Union operator.
Figure 297. Adding two cells.

When you list two cells and separate those cells with a colon, Excel will add up everything between and including the two cells.

The formula =SUM(B3:I3) returns the sum of every cell from B3 to I3. In this case, the color is the Range operator.
Figure 298. Specifying a range with a colon.

Everyone using Excel has undoubtedly seen the references as shown above.

There is a different type of reference called an intersection. In this case, you would separate two ranges by a space instead of a comma. =SUM(C2:C8 B3:I3) would give you all of the cells in common between the two ranges.

To see a useful example, it would help to add many range names to the worksheet. Follow these steps:

  • 1. Select A1:I8.

  • 2. From the Formulas tab, select Create From Selection.

The Defined Names group of the Formulas tab. Choose Create From Selection.
Figure 299. This creates many names using labels in the range.

  • 3. Leave Top Row and Left Column checked. Click OK.

The Create Names From Selection dialog box offers four choices: Top Row, Left Column, Bottom Row, Right Column. With A1:I8 selected, the dialog will create names from the months in the top row and the products in the left column.
Figure 300. Base the names on the left column and top row.

This will create 15 new range names. The name of Mar now refers to D2:D8. The name of ProdG now refers to B8:I8. This itself is a cool trick.

After Create Names from Selection, the name Mar refers to D2:D8, or all of the numbers for March.
Figure 301. Each column and each row get a name.

Going back to the intersection operator, a formula of =SUM(Apr ProdC) will return the intersection of the two ranges. This provides an interesting way to do a two-way loookup.

A formula of =SUM(Apr ProdC) tells Excel to take the intersection of the Apr range and the ProdC range. In this case, it will be a single cell, the 11 in E4. The space between Apr and ProdD is called the Intersection operator.
Figure 302. Only cell E4 is in both ranges. The result will be 11.

You can use Data Validation to add a dropdown to two cells. In one cell, someone could select a product. In another cell, someone could select a month.

Setting up a Data Validation drop-down. Choose to Allow a List. The source is the month names in B1:I1.
Figure 303. Add a dropdown for months.

The INDIRECT(J10) function tells Excel to go to J10 and the name of a range will be found in that cell. In the figure below, the formula in J12 is getting the intersection of ProdF and Apr, which returns the value of 20.

Choose a Product from the dropdown in J10. Choose a Month from the dropdown in J11. The result of the two-way lookup is returned using =SUM(INDIRECT(J10) INDIRECT(J11)).
Figure 304. Intersection of two ranges provides a two-way lookup.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ryan Besgrove on Unsplash