Nestled V Lookup?

[Veronica Guevara]

I have two data sets that I need to compare. I am not sure if it is best to use vlookup formula. I have one spreadsheet of hospital cases with discharge date that I am trying to compare to a report of outgoing phone calls. Both spreadsheets have a unique patient record number. I need to setup the formula on the hospital cases spreadsheet to do the following:

1. Search for unique medical record number in the outgoing phone call report

2. If unique medical record number is found, it should pull in the phone call that occurred AFTER the discharge date. The discharge date is on the hospital cases spreadsheet. 

When I use vlookup, it pulls in the first match but the first match isn't necessarily the phone call that took place after the discharge date. Sometimes it pulls in a telephone call that occurred prior to the discharge date. 

Please help!

[#2]

Can you send us a small example of the data you have? Use false names and dates and numbers, of course #GDPR

Then, I'm thinking about using RANK and/or LARGE to select the right datas you want to pull out. 

[#3]

Sample data attached.

Tab 1 has patient MRN, admit date, and discharge date

Tab 2 has patient MRN and telephone call date

[#4]

That could be like

=IFERROR(INDEX('Telephone Calls'!$B:$B,MATCH(1,INDEX(($A2='Telephone Calls'!$A:$A)*($C2<'Telephone Calls'!$B:$B),0,1),0)),"")

but the formula will take years to recalculate the sheet, better to limit the ranges

=IFERROR(INDEX('Telephone Calls'!$B$2:$B$20000,MATCH(1,INDEX(($A2='Telephone Calls'!$A$2:$A$20000)*($C2<'Telephone Calls'!$B$2:$B$20000),0,1),0)),"")

when it'll be more or less reasonable time.

As variant

=IFERROR(LOOKUP(2,1/($A2='Telephone Calls'!$A$2:$A$20000)/($C2<'Telephone Calls'!$B$2:$B$20000),'Telephone Calls'!$B$2:$B$20000),"")

but I'm not sure it'll be faster.

Even more better to transform the ranges into the tables, let say Data and Calls, when

=IFERROR(INDEX(Calls[Contact Date],MATCH(1,INDEX(([@MRN]=Calls[MRN])*([@[Dsch DT]]<Calls[Contact Date]),0,1),0)),"")