Help with dates formula

smerrick

Active Member
Joined
Feb 10, 2009
Messages
255
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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
How about this array formula

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Active Member
Joined
Feb 10, 2009
Messages
255
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,127,200
Messages
5,623,336
Members
415,968
Latest member
Chabal74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top