Using Multiple Criteria (Including Dates) for a Vlookup

[PaulMcCudden]

Hello All,

I'm having a problem finding a formula to do what I want. I would recommend taking a look at the sheets first, otherwise this could get confusing.

How the sheets are set up:
I'll attach a workbook to make it easier to understand, but what I have is two sheets, one has a list of hotel reservations for a specific group and the other has a list of restaurant reservations for the groups as well. Each group is listed multiple times (in restaurants, every day they traveling with us; in hotels, every time they change locations (or segment number)).

What needs to happen:
What I need to do is fill in the column, in the restaurant sheet, titled "Hotel the day BEFORE" with, you guessed it, the hotel they were in the day before. The problem is as far as I know there isn't an easy way to do this. What I have been working on (in the test sheet) is a formula that takes a variable and a date and looks at a data set and returns the value based on the matching variable and if that date is between two dates on the same line. However, I haven't found a good way to make this work for the day before.

Restrictions:
The data is going to be constantly being vertically rearranged, so no using cells above or below the reference cell.

Please let me know if you have any ideas/better ways to do this.

Thank you!