Index/Match (maybe?)

[Ken]

Excel2003 ...

Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in
"Asc" order.
Range D4100 ... 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

 

[T. Valko]

Try this:

=LOOKUP(1E100,D4100,B4:B100)

 

[Ken]

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]

You're welcome. Thanks for the feedback!

 

[Ken]

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,D4100,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]

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

 

[Ken]

T ... When in LOOKUP Help I was hung up on the following statement:

"If LOOKUP can't find the lookup_value, it matches the LARGEST value in
lookup_vector that is less than or equal to lookup_value."

LARGEST & LAST Value would have been the same IF my Date Col sorted in
Ascending order ... BUT since it isn't then Lookup is still returning the
LAST value.
(Which of course ... Is exactly what I wanted this Formula to do)

Do I have this resonably correct now or am I still missing something?

Thanks ... Kha

 

[T. Valko]

LARGEST & LAST Value would have been the same

IF my Date Col sorted in Ascending order

That's the "key" to understanding how this works. By default the LOOKUP
function *expects* the lookup_vector to be sorted in ascending order. The
help on LOOKUP is written based on that assumption.

However, if the data isn't sorted that way then the function doesn't work
the way it should. Some smart person discovered this "last value greater
than the lookup_value" and we've been exploiting this behavior ever since!

 

[Ken]

T ... I have 3 things to Thank you for here:

1: For sticking with me & walking me thru how this formula works ...
2: For Exploiting Excels various behaviors ...
3: For supporting this board ...

Thank you ... Kha

 

[T. Valko]

You're quite welcome!