Reverse 2D Lookup

[juanito]

I have a table of values with headings along the top and down the left-hand side. Months and soccer teams respectively, for instance.


I need a formula which returns both headings of the largest value. Thought this would be easy-ish, but I'm having a bad day...


Any Forum help will be much appreciated.


- Juanito

 

[Luke M]

Method might be similar to this idea:

http://chandoo.org/forums/topic/formula-to-find-cell-address


Instead of returning an address though, we can take the arguements that were giving us the row and column and use that to give us the values you want.


Headings in row 1:

=INDEX(1:1,MID(MAX(IF(B2:D10=MAX(B2:D10),VALUE(900&TEXT(COLUMN(B2:D10),"000")&ROW(B2:D10)))),4,3))


Labels in col A:

=INDEX(A:A,MID(MAX(IF(B2:D10=MAX(B2:D10),VALUE(900&TEXT(COLUMN(B2:D10),"000")&ROW(B2:D10)))),7,999))


Both of these are array formulas and should be confirmed using Ctrl+Shift+Enter, not just Enter.

 

[juanito]

Amazing, Luke: thanks!


- Juanito

 

[Luke M]

No problem, Juanito. Always glad to help. =)

 

[Sajan]

Hello,


Excel gives you lots of choices for a solution... so here is one more approach:

Assuming that your data is named "ScoresData"


The top header for the largest score is:

=INDEX($1:$1,MAX((ScoresData=MAX(ScoresData))*COLUMN(ScoresData)))

entered with Ctrl + Shift + Enter


The left header for the largest score is:

=INDEX($A:$A,MAX((ScoresData=MAX(ScoresData))*ROW(ScoresData)))

entered with Ctrl + Shift + Enter


Cheers,

Sajan.