Excel: Implicit Intersection

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

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