# Help with dates formula

#### smerrick

##### Active Member
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?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### xld

##### Banned

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

#### Andrew Poulsom

##### MrExcel MVP
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
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
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
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]

Replies
6
Views
196
Replies
3
Views
130
Replies
0
Views
88
Replies
3
Views
50
Replies
10
Views
379

1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

### 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?

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