conditional vlookup to find entry within date range

[coffee_man]

Hi, I have a list of operation details and admission details where the patient number is the unique identifier. I want to use vlookup to find the operation that matches the admission - that is, the operation date occured between the admission and discharge dates.

There can be multiple admissions for one patient, but I only want the one with the operation. When I use plain old vlookup I get the first admission in the list... so what I want to know is how can I vlookup based on a date range criteria? eg, if op date > adm date and < dis date

Thanks in advance!

Ruth

[NBVC]

Something like this maybe?

=INDEX($D$2:$D$100,MATCH(1,INDEX(($A$2:$A$100="X")*($B$2:$B$100>=DATE(year,month,day))*($B$2:$B$100<=DATE(year2,month2,day2)),0),0))

Where D2:D100 is the range of values to return if match found. A2:A100 is range to match for patient, B2:B100 is range with dates to match against start and end dates .

Adjust as necessary. This finds first row with patient id match and with date with start/end dates identified.

Note: You can replace "X", DATE(year,month,day) and DATE(year2,month2,day2) with cell references containing match criteria.

[coffee_man]

that looks good, and thank you, but I'm having trouble applying it to my sample data - would you mind showing me how it fits in the attached?

[gjlindn]

Is this what you're looking for?

[gjlindn]

Sorry...made a quick change. This one should be better.

[coffee_man]

whoops, just had a closer look... i wanted to change the date to date time, I made a new operation date time of a few minutes after the admission date time - it worked but I had another look at the formula and noticed that the range in the vlookups wasn't static - when I moved the op data around it stopped working for a couple of admission entries. I tried to anchor the range with the whole $ thing, but then it didn't work for some that it should when I copied the formula down the row... any clues?

[gjlindn]

Sorry about that! Just me being a little lazy replace the formula with this:

Please Login or Register  to view this content.

[coffee_man]

thanks for that - except it still isn't picking up the operation with a date time of a few minutes after admission. it is possible that I could encounter that scenario in my data. the weird thing is, I made my new operation time (2 minutes after admision) and it worked straight away - then I noticed the range in the vlookup was different. when I anchored it, it stopped working...

[gjlindn]

Could you repost an example workbook with the time included? Thanks!

[coffee_man]

here 'tis, ta

[gjlindn]

So I think this will fix the issue, but under the condition that two operations don't happen during the same Admission period. I added the COUNTIFS function as a way to force an error if there is no operation as well as if there are more than one in the same Adm. period. Also I put in a conditional format to display the instances where there are multiple Op dates in an Adm. period.

Please Login or Register  to view this content.

[NBVC]

or perhaps:

Please Login or Register  to view this content.

copied down after adjusting ranges to suit.

[coffee_man]

thank you both, I'll give them a spin with my many thousands of rows and see how it goes!
Cheers