vlookup or index with 2 criteria

[capri]

Hi,

I am trying to match records from 2 different extracts, but I need to use 2 fields as the criteria for the lookup. My first extract contains records of people presenting at a hospital emergency department. Each patient at the hospital is assigned a unique identifying number.

The second extract contains records of patients admitted to the hopsital. If a patient was admitted more than once in the period I am looking at then there would be multiple records for them. I would like to match the records where they first went to the emergency department and then were admitted. Therefore I need to include a date field in my lookup criteria.

I've done Vlookups and Index matches using one criteria, but was wondering how to structure if using 2 criteria or if it is possible. Would it be some kind of IF statemnt?

My fields are
URN
DepartDate
AdmitDate

I need the URN to be the same in both records, but I also need the DepartDate from Emergency to equal the AdmitDate from Admissions. The URN is in Column A of both extracts, while the Depart date is in Column I and the AdmitDate is in Column D

Thanks for any help.

capri

[HansV]

Insert a new column in column A in both extracts. Assuming that the first row contains column headings, enter a description such as LookupID in A1 in both.

In the Emergency extract, enter the following formula in A2:

=B2&"|"&J2

and fill down as far as needed. In the Admissions extract, enter the following formula in A2:

=B2&"|"&E2

and fill down as far as needed. You can now match the two extracts on column A, using =VLOOKUP(A2,...) or =MATCH(A2,...)

[capri]

Thanks Hans,

I would never have figured that out myself. Clever.


capri