Vlookup on satisfaction of certain conditions

[Ganesh Vankadaru]

Hie all,

I would be glad if I could get a solution to my problem in performing a vlookup operation in excel on satisfaction of certain conditions

I have attached 2 sheets named "Arrival register" and "Journal by Dept code".

"Arrival register" will have the details of the persons who have checked in along with the room no's , Date of check-in, Date of check-out, Check-in time, check-out time,Reservation number (unique field).

"Journal by dept code" contains the details of various charges posted under the room numbers at different points of time during their period of stay.

I need to get the reservation number in the arrival register to be vlooked up onto the other sheet.

One thing to be taken care is the time of charge in the Journal sheet and departure time of the person in the Arrival sheet as there are 2 persons occupying the same room on the same day only difference being the time of charge.

Please provide a solution to this problem ASAP.

Regards
Ganesh

 

[Xiq]

Hi Ganesh Vankadaru,

Please see attached.

Here is the formula I used:

=SUMPRODUCT((C2=Room_No)*(A2+B2<=Departure_Date+Dep_time)*(A2+B2>=Arrival_Date+Arr_time)*Reservation_Number)

 

[Xiq]

By the way,
It is not nice to say "Please provide a solution to this problem ASAP"

 

[Deepak]

Check this in F2 on "Journal by dept code" sheet

=INDEX('Arrival Register'!$A$2:$A$7,MATCH(1,INDEX((('Arrival Register'!$C$2:$C$7=C12)*(('Arrival Register'!$D$2:$D$7+'Arrival Register'!$F$2:$F$7)<=(A12+B12))*(('Arrival Register'!$E$2:$E$7+'Arrival Register'!$G$2:$G$7)>=(A12+B12))),,),0))

or

=SUMPRODUCT(('Arrival Register'!$A$2:$A$7)*('Arrival Register'!$C$2:$C$7=C13)*(('Arrival Register'!$D$2:$D$7+'Arrival Register'!$F$2:$F$7)<=(A13+B13))*(('Arrival Register'!$E$2:$E$7+'Arrival Register'!$G$2:$G$7)>=(A13+B13)))

 

[Ganesh Vankadaru]

Thank You so much Mr.Xiq.
Your solution worked.
And I am sorry I shouldn't have used it.!
Once again many thanks to you.