This can be a little confusing and might require reading it a few times!

If the lookup_value is greater than any value in the lookup_vector the
formula will "match" the *last* value in the lookup_vector that is *less
than* the lookup_value.

1E100 (or 1E+100) is scientific notation for a very large number, 1 followed
by 100 zeros. This is a shorthand method of expressing 10000000(imagine a
string of 100 zeros).

Since the lookup_vector contains dates it is guaranteed that the
lookup_value will be greater than any value in the lookup_vector so it
matches the *last* value in the lookup_vector. The formula then returns the
value from the result_vector that corresponds to the *last* value in the
lookup_vector that is *less than* the lookup_value.


exp101
--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
T ... Formula does exactly as requested & seems simple enough ... However,
I
spent some time in the HelpScreen for LOOKUP & I am still at a loss for
how
the "IE100" part of this formula works???

=Lookup(1E100,D4:D100,B4:B100)

I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same
row Col B. Thing is ... the large # can not be found, so I thought Lookup
would return next highest value ... This made sense until "dates" were out
of
order & your formula still worked flawlessly.

Above said ... could you provide a little guidance on how this formula is
working.

Thanks ... Kha

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
T.

The Excel solutions you provide here are extremely helpul ... I stalled
on
the -1E100 - piece of your formula, but then remembered that "dates"
are
really numbers.

Thank you for supporting these boards ... Your many solutions are
greatly
appreciated ... Kha

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,D4:D100,B4:B100)


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Excel2003 ...

Range B4:B100 ... Contains random non-repeating "Text" numbers
sorted
in
"Asc" order.
Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy)

Need formula in Cell D3 to return Col B value found against "last"
date
(not
latest date)

ie:

Col B ... Col D

10 .... 04/22/08
20 .... 06/25/08
35 ....
45 ....
50 .... 05/01/08 ... (need 50 to be returned)
62 ....
Etc

Thanks ... Kha