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