• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reverse 2D Lookup

juanito

Member
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

Excel Ninja
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.
 

Sajan

Excel Ninja
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.
 
Top