vlookup two value between dates

[Armitage2k]

Hi,

Need some help with a formula here please...

I have a list of food consumption postings, remarking the posting date and room number of the guest who consumed the food. Now I have another report with the arrival, departure and number of adults per room. I would like to link those two reports together by both matching the ROOM NUMBER as well check if the POSTING DATE is between the the arrival and departure date of the room. If yes, I would need the formula to return the total number of adults in the room.

So in short:
* Match both reports by room number AND see if posting date is between arrival / departure
* return the number of adults from Data 1 sheet to Data 2 sheet

I have attached a sample and would love to get some guidance on this
Thanks,

A2k

Lookup Sample.xlsx

[FDibbins]

See if this will work for you?

=IF(AND(VLOOKUP(D2,$B$19:$G$31,3,0)<=B2,VLOOKUP(D2,$B$19:$G$31,4,0)>=B2),VLOOKUP(D2,$B$19:$G$31,6,0),"")

[sktneer]

Or this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

In F2

Please Login or Register  to view this content.

and copy down.

[samba_ravi]

In Data 1 Sheet Row No. 6 & Row No. 12 have same Room Number 308 with different arrival and Departure Data, in this situation what to do

[Armitage2k]

Thanks guys, sktneer's array fixed it for me.

Sorry about the incorrect sample data, logically that was an error from my side since one room can only be occupied by one reservation at a time. Anyway, its working

Thanks a lot!
A2k

[sktneer]

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved. (I hope you know how to do that.)