software

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: www.excelforum.com/member.php...oamp;userid=32595
View this thread: 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)lt;gt;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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: 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: www.excelforum.com/member.php...oamp;userid=32595
View this thread: www.excelforum.com/showthread...hreadid=523973Hi!

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

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

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 amp; 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: www.excelforum.com/member.php...oamp;userid=32595
View this thread: www.excelforum.com/showthread...hreadid=523973Here's a sample file based on my example:

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

Biff

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

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

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

ABCDE
1009998100100

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,quot;smckiequot; wrote:

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