Help with dates formula

[smerrick]

A1:A23 are dates from 15/12/09 to 06/01/10. Column B will have random numbers, up to 5 decimals long next to these dates.

What I need to do is to input a whole number in cell G2 (taget cell) and from this it will give me the most recent date at which the data is equal to or less than the target cell (into cell H2).


Can this be done via a simple formula?

 

[xld]

How about this array formula

=MIN(IF(MIN(IF(B1:B23<=G2,ABS(TODAY()-A1:A23)))=ABS(TODAY()-A1:A23),A1:A23))

 

[Andrew Poulsom]

Try:

=MAX(IF(B1:B23<=G2,A1:A23))

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surrond this array formula with curly braces {}.

 

[DonkeyOte]

If the dates are always listed in sequence of earliest to most recent another option might be:

=LOOKUP(2,1/(B1:B23<=G2),A1:A23)

If unsorted and you wanted to avoid Array for no reason other than avoiding Array

=ABS(MIN(INDEX((-A1:A23)*(B1:B23<=G2),0)))

 

[smerrick]

This is to DONKEYOTE

I am curious about your non-array formula (ABS(MIN(INDEX((-A1:A23)*(B1:B23<=G2),0))) ). It's a novel approach, but I was wondering, why the ABS / - MIN syntax? I would have thought

=MAX(INDEX((A1:A23)*(B1:B23<=G2),))

would be sufficient as multiplying by TRUE or FALSE will ensure that A1:A23 is a number (if this is the reason)..?

Thanks for your help

 

[DonkeyOte]

smerrick, you're quite right ... I appear to have posted a lot of garbage yesterday - apologies for confusion caused
(I'm blaming it on my cold!)

[on larger datasets I suspect the INDEX approach would perform poorer than any Array]