Paul said:
Sorry should of explained that D1 is based on date in C1 looking up nearest
match in Column B (likewise for D2)
Do you really mean "nearest"? That is, if the date in C1 is more than
half way between two dates in column B, choose the latter; but if it is
less than half way between the two dates, choose the former. (And who
knows what you want if C1 is exactly half way.) I hope that is not
what you mean.
I wrote as a WAG:
Let me try to restate what you want. You want to search column B for
the closest date on or after the date in C1. In D1, you want the
matched date; in D2, you want the date from the row before the matched
date. Is that about right?
Actually, it appears that the loopup functions are better suited for
doing this: search column B for the closest date on or __before__ the
date in C1. If that is what you want, then:
D1: =offset(B1, lookup(C1, B1:B4, A1:A4), 0)
D2: =vlookup(C1, B1:B4, 1)
That relies on the fact that in column A, you put the relative row
numbers of the table entries in column B. Even if that does not apply
directly to your problem statement (which is still ill-defined, IMHO),
hopefully it gives you the tools for crafting your own solution.
I also wrote earlier:
And what if the matched date is B2, the first row of the table
searched? There is no "previous date" in that case for D2.
"B2" was a typo. It should be B1.
The latter two boundary conditions can probably be handled by using
ISERROR() in an IF() function. But they should be covered in a
complete solution -- or you might assure us that column B will be
designed so that those conditions cannot happen, in deference to
getting a more tractable solution
It would be better to design the table in column B so that the boundary
conditions never arise, assuming valid input in C1. That is, for the
"on or before" search that I speculated, column B should contain a date
that is equal to or before any valid date that might be in C1, and
column B should contain a date that is after any valid date that might
go into C1. Alternatively, you could write the following, for example:
D2: =if(iserror(vlookup(C1, B1:B4, 1)), "", vlookup(C1, B1:B4, 1))
But the similar formula for D1 becomes __very__ messy because you must
account for the last date in column B. As I said, simply design table
B with appropriate values to avoid the boundary conditions. They can
be hidden rows, if you like.
-----