Find exact or closest lower date

Can u explain why 1st april's expected result is 3 (F2)?

Can u explain why 1st april's expected result is 3 (F2)?

the closest date to 1-Apr-09 in column B with a "C" in column A is 4-May-09, which is in the third row of the data table. Thus the result is 3

With the data table in columns A and B not sorted, it will be be virtually impossible to use any kind of lookup or match function to establish the closest match.

Elmer, can the order of the data in A and B be changed? Otherwise, your call for a solution without a macro and without helper columns will probably not come to any result.

aaaaaaa
understand.... thx...

Try this one
Array entered

Please Login or Register  to view this content.

Copy of Book4(1).xls

Here's my solution and contaminedwithexcel...

We both have errors...

Contamined: I started with ABS but it won't work.. He needs first less value than 0...

So if dates differences are -3, -2, 1 he need to return -2 ( I guess).

i have problem with matching data... I find correctly dd.mm.yy for a specific category (C,D,E) but if there's some same date before he return that row...

ContaminatedWit: Close, but not quite.

copy from row 2 down to row 11 and see that it does not meet the expected results in a few rows.

Elmer, I don't understand why F5 should be 11. The closest date to 6-may-09 with a C in the first column would be 4-may-09, so the result would be 3

This is what ContaminatedWit's formula delivers.

So, is your sample data correct? If so, you've got some more explaining to do.

I really can't find errors. Where they are?

But closest value for 28 April in column E is 27 April in column B, They both have "D" suffix... what wrong here?

it's frustrating to see that the OP is online all the time we're wrecking our brains, but can't be bothered to check back and contribute....

‏
If I wasn't clear enough in what I explained inside my last attachment let me try again:

1) Col. A+B hold the raw data.
2) Col. C presents the expected results, to be drawn from the table in G2:J13.
These results are, for now, typed by hand! and there may be some mistakes but in general this what the requested formula should return.
3) Col. D holds my trial formula - which should return the col. C values but, unfortunately, it is not good enough as it returns N/A errors in some cases .

Thanks, Elm