Tom’s Tutorials For Excel: Reverse Intersect Lookup for Headers
I recently posted this example, followed by this more advanced example for finding an intersecting value in a table with multiple rows and columns.
Today it’s the opposite scenario, where you identify a value in the table and you lookup the value’s row header, column header, and while we’re at it, the address of the value. In the picture, the maximum number is identified, with its address, and header labels for Month and Salesperson.
The formula in cell A13 for most annual sales is
=MAX(B2:M9)
The formula in cell A15 for the Month of most annual sales is
=INDIRECT(ADDRESS(1,SUMPRODUCT((B1:M9=A13)*COLUMN(B1:M1))))
The formula in cell A17 for the Salesperson with most annual sales is
=INDIRECT(ADDRESS(SUMPRODUCT((B1:M9=A13)*ROW(B1:B9)),1))
The formula in cell A19 for the cell address of the most annual sales is
=ADDRESS(SUMPRODUCT((B1:M9=A13)*ROW(B1:B9)),
SUMPRODUCT((B1:M9=A13)*COLUMN(B1:M1)),4)
Note, this formula is broken into two lines to accommodate all web browsers. You enter it as a single line statement as any formula.
Leave a Reply