Notice that the formulas in the previous two figures were outside of the range B:I. Those formulas would not work in that area due to a feature called "Implicit Intersection". Here is how it works.
The named range ProdF runs from B7:I7. If you enter a formula anywhere in columns B through I and that formula references ProdF, you will only get the value from that column of ProdF. In the image below, a formula of =ProdF in D10 returns the 13 from cell D7.
- This formula returns the cell from ProdF that intersects with the formula.
This clearly is not intuitive. In my Power Excel seminars, I occasionally find people who are taking advantage of the formula, but few are doing it knowingly. In a similar fashion, a formula of =Apr anywhere in rows 2:8 will return only the April sales from that row.
This feels like the old Natural Language Formulas in Excel 2003, but it is a different feature.