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

Retrieving the Column Header and the Row Header of the Max Value in a table

G

GFC

Member
I would like to get with a excel formula (No VBA) the header of the column and of the row that contain the max value in a table.
Ideally I would also check if there is only one max value and else indicate that the answer is not unique. Ideally I would also be able to find the Xth largest value.
 
Maybe to clarify the question:
My value are between A4 and D7

To get the max=MAX(B5:D7)
To get the header per row or per column it works:
=INDEX($B$4:$D$4;MATCH(MAX(B5:D5);B5:D5;0))
=INDEX($A$5:$A$7;MATCH(MAX(B5:B7);B5:B7;0))

But how to get a correct answer from the match function when the array has more than one row?
 
@GFC ... To answer the first part of your question use below senario.

I had assume the row header in A4:A7. Column header in B3:D3. Data in B4:D7.
Calculate max in G4: =MAX(B4:D7)
Row header in G8: = INDEX(A4:A7,SMALL(IF($B$4:$D$7=$G$4,ROW($B$4:$D$7)-ROW($B$4)+1),1)) enter with Ctrl+Shift+Enter as this is an array formula.
Column Header in G9: =INDEX(B3:D3,SMALL(IF($B$4:$D$7=$G$4,COLUMN($B$4:$D$7)-COLUMN($B$4)+1),1)) (again enter with Ctrl+Shift+Enter)

Both these formulas will work for unique max value.

Kindly, explain the second part of your question, to make the solution suit your requirement.

Regards!
 
Dear GFC

To expand on Somendra's excellent formulas

=INDEX(A4:A7,SMALL(IF($B$4:$D$7=LARGE($B$4:$D$7,$A$3),ROW($B$4:$D$7)-ROW($B$4)+1),1))
will find Row header for the Xth largest (where X is stored in A3)

Similarly fpr the column header
=INDEX(B3:D3,SMALL(IF($B$4:$D$7=LARGE($B$4:$D$7,$A$3),ROW($B$4:$D$7)-ROW($B$4)+1),1))
again both are array formulas

To determine in the maximum value is unique or not the following will do that

=IF(MAX(B4:D7)=LARGE(B4:D7,2),"Not Unique","Unique")
 
Another approach using sumproduct..
However.. it fails.. if more than one data found..

=INDEX(LeftHeader,IF(SUMPRODUCT((TDArray=FindVaL)*1)<>1,NA(),SUMPRODUCT(ROW(TDArray)*(TDArray=FindVaL)))-ROW(LeftHeader)+1)&"-"&INDEX(TopHeader,,IF(SUMPRODUCT((TDArray=FindVaL)*1)<>1,NA(),SUMPRODUCT(COLUMN(TDArray)*(TDArray=FindVaL)))-COLUMN(TopHeader)+1)

or..

=ADDRESS(IF(SUMPRODUCT((TDArray=FindVaL)*1)<>1,NA(),SUMPRODUCT(ROW(TDArray)*(TDArray=FindVaL))),IF(SUMPRODUCT((TDArray=FindVaL)*1)<>1,NA(),SUMPRODUCT(COLUMN(TDArray)*(TDArray=FindVaL))))

Check the attached for more detail..
 

Attachments

You must log in or register to reply here.
Back
Top