Excel: 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.

1. 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.

   

2. 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.

   

3. 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.

   

4. This creates many names using labels in the range.
   3. Leave Top Row and Left Column checked. Click OK.

   

5. 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.

   

6. 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.

   

7. 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.

   

8. 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.

   

9. Intersection of two ranges provides a two-way lookup.