I'd use the much simpler
=LOOKUP(2,1/(D6:D10<0),D6:D10)

That will fail if the range isn't sorted in ascending order.

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

It's making very hard work of looking up a value (2) it wont find in the
range so it will return the last instance of the maximum value.

This bit
=LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0

returns either a 1 for the max value or div/0 error for anything else but
because it's looking up 2 it returns the last instance.

But, that's irrelevent because why does it matter if the first ot last
instance of max is returned. It would matter if the formula was like this

=LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),E6:E 41)

In which case it finds the last max in D and returns form column E which
is
why I said it's making hard work of it.

I'd use the much simpler
=LOOKUP(2,1/(D6:D10<0),D6:D10)

which does exactly the same and also works with the offset
=LOOKUP(2,1/(D6:D10<0),E6:E10)


Mike
"Dave F" wrote:

Can someone please explain to me what this formula is doing?

=LOOKUP(2,1/((COUNTIF(D6:D41,""&D6:D41)=0)*(D6:D41<"")),D6:D 41)

Thanks.

Dave