Hi Milos
you're right. It is array entered. Missed that due to my small number
of testing data
Frank

Milos Setek wrote:
Hello again Bob and Frank,

The below formula is an array one, since I had to press
ctrl+shift+enter to get it working. However, it is clever.

The reason why I am asking this is because mine one gives me a
#VALUE! error when run from within a VBA macro, but manually it works
so I am not sure what is going on, and hence I wanted to try
something either simpler or non-array based.

Thank you all for your efforts.

Milos

"Frank Kabel" wrote in message
...
Bob Phillips wrote:
Hi Frank,

Missed that subtle nuance. INDEX(MATCH?


Yeah
I tried that but the result is even more complicated than the OP's
solution. I only got to this non array solution:
=SUMPRODUCT((ABS(range)=MAX(ABS(range)))*(range))
not simpler but a non array one :-)

Frank