get information from a sheet with various parameters for a specifique line

[OM.NAMNGUM]

Good Morning everyone

English is not my first language so in advance please do excuse me if I'm not fully understandable =)

here is my problem, half solved :

I have a workbook with many sheets, which includes all the information of the guests and all the transaction of an hotel.

in a first sheet there is name, nationality, room number, check in/out date.... for every guest over a month
One a second sheet, i have the bill from our restaurant with include the following details : room number, date, price, payment type....

Recently my dear and beloved boss asked me the repartitions of the sales by nationality and market segment.

So theoretically it should be possible to do some Vlookup and other, with the complication that the date of sale should be included between the check in and check out date for a particular room. So far, my tentative of using Vlookup have been solved by a #N/A

In another hand i had a result with a IF/AND formula as following
=IF(AND($A8>='Reservation Sumery'!$K$4:$K$691,'FB bill Resume'!$A8<'Reservation Sumery'!$L$4:$L$691,'FB bill Resume'!$B8='Reservation Sumery'!$J$4:$J$691),'Reservation Sumery'!$M$4:$M$691,"")

But there is a problem. Lets say that i have 4 lines (1,2,3 and 4) of bill restaurant with the same date and room number. The formula works ONLY if the information about this room are in same line on the other sheet

So if the room A is on the line n"2 of the reservation sumery, and the bills of this room are on the line n"1,2,3 and 4 of the FB bill resume sheet, i get a result only for the line number 2...

Thanks in advance
Theo

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[OM.NAMNGUM]

Dear JohnTopley

Thank you for time and consideration!

here are a sample of what i need, hope it will help you to help me! =)

[JeteMc]

Hello OM.NAMNGUM and Welcome to Excel Forum.
If I understand correctly the following array entered formula* may do what you want.
1) Select cell G4 on the FB bill Resume sheet,
2) Paste the following into the formula bar:

Please Login or Register  to view this content.

*3) Simultaneously press the Ctrl, Shift and Enter keys,
4) Drag the fill handle down to cell G10.
Let us know if you have any questions.

[OM.NAMNGUM]

Hello JeTeMc

Thank for your help, its working perfectly, problem solved!

If you ever come to Laos, come to the Sanctuary Nam Ngum Beach resort, i own you some drinks =)

Theo

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. If I am ever in Laos I will certainly look forward to a visit at the Sanctuary Nam Ngum Beach resort. I hope that you have a blessed day.