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.
- 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.
- 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.
- 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:
- Select A1:I8.
- From the Formulas tab, select Create From Selection.
- This creates many names using labels in the range.
- Leave Top Row and Left Column checked. Click OK.
- 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.
- 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.
- 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.
- 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.
- Intersection of two ranges provides a two-way lookup.