Max Value in a Row

[smckie]

Hello All,

I am trying to find the largest value in a row 78 and once that value is found place the column header name (In Row 2) for that largest value found back to the cell. Then I will find the 2nd largest value in the same row... etc The following commands work (Sometimes):

=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,1),$78:$78,0)),-76,0,1,1)
=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,2),$78:$78,0)),-76,0,1,1)
=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,3),$78:$78,0)),-76,0,1,1)
=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,4),$78:$78,0)),-76,0,1,1)

In the case where there is more than one cell in row 78 with the same value the function returns the first cell that satifies the equation returning the same column name. I would like to be able to step to the next column with the same value and return that columns name. So in the case where there are three columns that have the same value in row 78, I would like to see the names of all three columns.

Any ideas? Any help would be greatly appreciated

Regards

Scott

[daddylonglegs]

I don't believe there's a simple formula solution but here's one possibility...

assuming that your column headers in row 2 are all unique

this formula in A6

=INDEX($2:$2,MATCH(MAX($78:$78),$78:$78,0))

this formula in A7 copied down column

=IF(LARGE($78:$78,ROW()-ROW(A$6)+1)<>LARGE($78:$78,ROW()-ROW(A$7)+1),INDEX($2:$2,MATCH(LARGE($78:$78,ROW()-ROW(A$6)+1),$78:$78,0)),INDEX(INDEX($2:$2,MATCH(A6,$2:$2,0)+1):IV$2,MATCH(LARGE($78:$78,ROW()-ROW(A$6)+1),INDEX($78:$78,MATCH(A6,$2:$2,0)+1):IV$78,0)))

note that you will need to amend the formulas accordingly if entered in different cells

[smckie]

Hey thanks for your time... Couldn't get it to work. Seems like such a simple task but ahhh well, what can I do.

Thanks Again... Enjoy your weekend

Scott

[Biff]

Hi!

Try this:

Array entered:

=INDEX($2:$2,MATCH(LARGE($78:$78+COLUMN($78:$78)/10^10,ROWS($1:1)),$78:$78+COLUMN($78:$78)/10^10,0))

Copy down as needed.

In case of ties, the rightmost value will be returned first:

....A.......B......C......D.......E
100.....99.....78.....85.....100

The results would be:

E
A
B
D
C

Biff

"smckie" <[email protected]> wrote in
message news:[email protected]...
>
> Hello All,
>
> I am trying to find the largest value in a row 78 and once that value
> is found place the column header name (In Row 2) for that largest value
> found back to the cell. Then I will find the 2nd largest value in the
> same row... etc The following commands work (Sometimes):
>
> =OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,1),$78:$78,0)),-76,0,1,1)
> =OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,2),$78:$78,0)),-76,0,1,1)
> =OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,3),$78:$78,0)),-76,0,1,1)
> =OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,4),$78:$78,0)),-76,0,1,1)
>
> In the case where there is more than one cell in row 78 with the same
> value the function returns the first cell that satifies the equation
> returning the same column name. I would like to be able to step to the
> next column with the same value and return that columns name. So in the
> case where there are three columns that have the same value in row 78, I
> would like to see the names of all three columns.
>
> Any ideas? Any help would be greatly appreciated
>
> Regards
>
> Scott
>
>
> --
> smckie
> ------------------------------------------------------------------------
> smckie's Profile:
> http://www.excelforum.com/member.php...o&userid=32595
> View this thread: http://www.excelforum.com/showthread...hreadid=523973
>

[smckie]

Biff... Thanks for the Reply

Do you think you could write the eqation as it applies to your example?

A B C D E
100 99 78 85 100

Where the value of 100 is in Cells A2 & E2.

I couldn't even get it to work with a simplified spreadsheet. I cannot see how the array works???

Thanks

Scott

[Biff]

Here's a sample file based on my example:

http://s60.yousendit.com/d.aspx?id=2...F0PR9RZA0ZHON6

Biff

"smckie" <[email protected]> wrote in
message news:[email protected]...
>
> Biff... Thanks for the Reply
>
> Do you think you could write the eqation as it applies to your
> example?
>
> A B C D E
> 100 99 78 85 100
>
> Where the value of 100 is in Cells A2 & E2.
>
> I couldn't even get it to work with a simplified spreadsheet. I cannot
> see how the array works???
>
> Thanks
>
> Scott
>
>
> --
> smckie
> ------------------------------------------------------------------------
> smckie's Profile:
> http://www.excelforum.com/member.php...o&userid=32595
> View this thread: http://www.excelforum.com/showthread...hreadid=523973
>

[Ashish Mathur]

Hi,

You may try the following array formula (Ctrl+Shift+Enter):

The data is laid out as below in range A2:E3:

A B C D E
100 99 98 100 100

In cell A5, enter the following array formula (Ctrl+Shift+Enter):

INDEX($A$2:$E$3,1,SMALL(IF($A$3:$E$3=LARGE($A$3:$E$3,1),COLUMN($A$3:$E$3)),COLUMN()))

Now copy across columns.

Hope this helps.

If you have any further queries, please feel free to contact me.

Regards,


"smckie" wrote:

>
> Biff... Thanks for the Reply
>
> Do you think you could write the eqation as it applies to your
> example?
>
> A B C D E
> 100 99 78 85 100
>
> Where the value of 100 is in Cells A2 & E2.
>
> I couldn't even get it to work with a simplified spreadsheet. I cannot
> see how the array works???
>
> Thanks
>
> Scott
>
>
> --
> smckie
> ------------------------------------------------------------------------
> smckie's Profile: http://www.excelforum.com/member.php...o&userid=32595
> View this thread: http://www.excelforum.com/showthread...hreadid=523973
>
>