Why Use the Intersection Operator?
January 27, 2022 - by Bill Jelen
![Why Use the Intersection Operator? Why Use the Intersection Operator?](/img/excel-tips/2022/01/why-use-the-intersection-operator.jpg)
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.](/img/content/2022/01/LE10000283.jpg)
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.](/img/content/2022/01/LE10000284.jpg)
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.](/img/content/2022/01/LE10000285.jpg)
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.](/img/content/2022/01/LE10000286.jpg)
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.](/img/content/2022/01/LE10000287.jpg)
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.](/img/content/2022/01/LE10000288.jpg)
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.](/img/content/2022/01/LE10000289.jpg)
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.](/img/content/2022/01/LE10000290.jpg)
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)).](/img/content/2022/01/LE10000291.jpg)
This article is an excerpt from Power Excel With MrExcel
Title photo by Ryan Besgrove on Unsplash