Max Value in a Row

[smckie]

Hello All,

I am trying to find the largest value in a row 78 and once that valu
is found place the column header name (In Row 2) for that largest valu
found back to the cell. Then I will find the 2nd largest value in th
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 sam
value the function returns the first cell that satifies the equatio
returning the same column name. I would like to be able to step to th
next column with the same value and return that columns name. So in th
case where there are three columns that have the same value in row 78,
would like to see the names of all three columns.

Any ideas? Any help would be greatly appreciated

Regards

Scot

 

[daddylonglegs]

I don't believe there's a simple formula solution but here's on
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 i
different cell

 

[smckie]

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

Thanks Again... Enjoy your weekend

Scot

 

[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]

Biff... Thanks for the Reply

Do you think you could write the eqation as it applies to you
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 canno
see how the array works???

Thanks

Scot

 

[Biff]

Here's a sample file based on my example:

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

Biff

 

[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,