This is simply testing to make sure the cells are not blank. You may not
need that for your application but I put it in there out of habit! There's
really no difference with it or without it except when every cell in the
range is blank.

Biff,
I did an experiment with
=LOOKUP(2,1/((COUNTIF(D6:D12,""&D6:D12)=0)*(D6:D12<"")),D6:D 12)
and
=LOOKUP(2,1/((COUNTIF(D6:D12,""&D6:D12)=0)),D6:D12)

With test data: A, B, C, D, A, B, C both gave D as expected
With just one blank the first still gave D but the second gave 0: but
sometimes it did give the right result - very odd

all the best
--
Bernard

"T. Valko" wrote in message
...
A little known fact about COUNTIF is that it evaluates TEXT for equality
the same way it evaluates numbers.

A
B
D
C

=COUNTIF(A1:A4,"A") = 3
=COUNTIF(A1:A4,"D") = 0

You wanted to return the "largest" text entry in the range.

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

Here's how the formula works...

In the above formula LOOKUP uses 3 arguments:

lookup_value = 2
lookup_vector = 1/((COUNTIF(A1:A4,""&A1:A4)=0)*(A1:A4<""))
result_vector = A1:A4

Let's dissect the lookup_vector...

Since we want the largest text entry we "know" that the largest text entry
will have a COUNTIF result of 0 meanng there are no entries greater than
the largest text entry. See the COUNTIF formula above.

The COUNTIF will return an array of results:

COUNTIF(A1:A4,""&A1) = COUNTIF(A1:A4,"A") = 3
COUNTIF(A1:A4,""&B1) = COUNTIF(A1:A4,"B") = 2
COUNTIF(A1:A4,""&C1) = COUNTIF(A1:A4,"D") = 0
COUNTIF(A1:A4,""&D1) = COUNTIF(A1:A4,"C") = 1

We then test these results for equality to 0:

COUNTIF(A1:A4,"A") = 3 = 0 = FALSE
COUNTIF(A1:A4,"B") = 2 = 0 = FALSE
COUNTIF(A1:A4,"D") = 0 = 0 = TRUE
COUNTIF(A1:A4,"C") = 1 = 0 = FALSE

This array is then multiplied together with this array: (A1:A4<"")

This is simply testing to make sure the cells are not blank. You may not
need that for your application but I put it in there out of habit! There's
really no difference with it or without it except when every cell in the
range is blank.

(A1<"") = TRUE
(B1<"") = TRUE
(C1<"") = TRUE
(D1<"") = TRUE

{F;F;T;F}*{T;T;T;T} ={0;0;1;0}

Ok, now comes the confusing part!!!

The way that LOOKUP works is:

If the lookup_value is greater than any value in lookup_vector, it will
match the *last* value in the lookup_vector that is less than the
lookup_value. The lookup_value is 2 and the lookup_vector is 0;0;1;0. The
lookup_value 2 *IS* greater than any value in the lookup_vector so it will
match the *last* value in the lookup_vector that is less than 2 and that
value is the last 0.

Now comes the really confusing part!!!

The *last* value that is less than the lookup_value 2 is the *last* 0.
However, this can't be our match since 0 means this entry failed to meet
the conditions of the COUNTIF=0 and/or the test for blank. So, we need to
do something about that.

LOOKUP ignores error values so we are going to use that to our advantage.
We divide 1 by the the array {0;0;1;0} knowing that any number divided by
0 returns the #DIV/0! error. So:

1/0 = #DIV/0!
1/0 = #DIV/0!
1/1 = 1
1/0 = #DIV/0!

So that becomes the array for the lookup_vector (D=#DIV/0!):

{D;D;1;D}

Now, the *last* value in the lookup_vector that is less than the
lookup_value 2 is 1.

So the final result of the formula is the entry in the result_vector which
is the range A1:A4 that corresponds to 1.

=LOOKUP(2,{D;D;1;D},{"A";"B";"D";"C"})

=D

D is the largest TEXT entry in the range.

So there you have it!


exp101
--
Biff
Microsoft Excel MVP


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