Offset Lookup

[Number7stunner]

Hey everyone! I am looking to find a date up or down from a specific date in a table. I attached an excel to make it easier. Basically trying to look up and down (offset) using a date that I write down. Hope someone can help! Thanks in advance!

[Jonmo1]

Try

=OFFSET(INDEX($E$6:$E$25213,MATCH(J6,$E$6:$E$25213)),120,0)
and
=OFFSET(INDEX($E$6:$E$25213,MATCH(J6,$E$6:$E$25213)),-120,0)

[TudyBTH]

Hi,
try this for "120 Days Back"

Please Login or Register  to view this content.

and this for "120 Days Forward

Please Login or Register  to view this content.

"

[Glenn Kennedy]

This will return D-120:
=INDEX($E6:$E$25213,MATCH(J6-120,$E$6:$E$25213,1))

This will return D+120:
=INDEX($E$6:$E$25213,MATCH(J6-120,$E$6:$E$25213,1))

There is no exact match for some values in your list (e.g. 08/11/32 +120 = 08/03/33 = missing). I have set the formual to returning the matching date or the first matching date EARLIER than the required one. If you'd prefer it to show a blank, use this:

=IFERROR(INDEX($E$6:$E$25213,MATCH(J6-120,$E$6:$E$25213,0)),"")

[Jonmo1]

That's not quite the same Glenn.

I think the op wants search column E for the date listed in J, and offset 120 'ROWS' from that cell.
The list of dates in E is NOT all inclusive, it's skipping many dates.
So just subtracting 120 from the date in J is going back 120 DAYS, not the same thing.

AFter looking a little more, it appears the missing dates from E are weekends and holidays.
If that's the case, then the WORKDAY function is much simpler, without the need for the long list of dates.

=WORKDAY(J6,-120,$A$1:$A$100)

Where A1:A100 is a list of the holiday dates to exclude.

[Number7stunner]

Wow everyone came in quick! Thanks as it works!

[Glenn Kennedy]

Jonmo... you may well be right. We'll see...

[Jonmo1]

You're welcome.