Lookup, Index, Match, Array Help

Good Evening All,

First off would like to say thanks for all the help everyone has given me so far, this forum has kept me sane with all my Excel problems.

I have a somewhat annoying problem that has been hounding me for the last few days that I can't seem to solve. I have tried various lookups, index/ match combos and even an array. The array worked but when using it for the whole year it crippled my computer so this isn't exactly viable.

I am trying to create an accommodation spreadsheet for various houses with so many rooms in each house coupled with an input sheet that will reside on a separate sheet or even a completely different book.

I want to be able to enter the date, guest, hotel and room and for the sheet to be automatically populated with the guests name. I have attached a sheet to hopefully best describe my problem.

Any help is much appreciated.

Regards