LOOKUP Function with Conditions: i.e. Lookup only if Date 1 is > than Date 2

confused:Hello,

I have two sets of client data. The data represents admissions and discharges into 2 different facilities (Facility A and Facility B). Facility A is a hospital and Facility B is a Residential Care Facility. The purpose of this analysis is to determine which clients from Facility B have been admitted into Facility A during their stay at Facility B.

Person 1 could have been admitted and discharged more than 1 time to Facility A as well as the Facility B. I did a VLOOKUP between the two data sets to see which clients are on both sets of data; however, I need to determine if in fact person 1 was actually in Facility B at time of admission to Facility A.

I am not sure if there is such at thing as a function that will look these clients up only if the Facility B (Res Care) Admit Date is less than the Facility A (acute) admit date AND the Facility B discharge date is greater than the Facility A admit date???