Combine vlookup or index/match with offset

Hello All,

I'm hoping someone can help me over the line with something - I've used offset before with consistent references, but I now want to find the beginning of my offset by a specific value.

I attached an example sheet - for each date value in the second table I want to find the corresponding date value in the first table and sum the value next to it backwards 5 days. E.g. for the 25/01/17 I want to sum the values for the 25th back to the 21st.

I want it this way in case the sum is not always carried out on the 5th day (though I will always want the sum of the previous 5).

I have read around it and seems the consensus is that a vlookup would be the wrong thing, I've tried a combination of index and match too, but can't get even them to talk to me either.

Any suggestions welcome.

Thanks in advance.

Simon.

The first one worked perfectly. Took me a while to understand the start point reference for the offset when I put it into my real sheet but I got there. Thanks for your help Sandy.

Glad to help

Thanks for feedback

Btw. have you get how second formula works?

You are welcome
IFNA is a little shorter but works with #N/A only, IFERROR is longer but works with more errors.
I am glad it works to you.