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
vlookup or index with 2 criteria
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: vlookup or index with 2 criteria
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,...)
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,...)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 87
- Joined: 20 Jan 2011, 06:42
Re: vlookup or index with 2 criteria
Thanks Hans,
I would never have figured that out myself. Clever.
capri
I would never have figured that out myself. Clever.
capri