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

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=523973

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


--
smckie
------------------------------------------------------------------------
smckie's Profile: http://www.excelforum.com/member.php...o&userid=32595
View this thread: http://www.excelforum.com/showthread...hreadid=523973

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" wrote in
message ...

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

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

Here's a sample file based on my example:

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

Biff

"smckie" wrote in
message ...

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

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