Matching closest date with two sets of data

[strevs]

Hi Excel Wizards,

I'd really appreciate your help with this one.

I have two sets of data, the first has a list of PersonIDs and referral dates, the second has a list of IDs with assessment dates. The assessment can take place before, on the same day or after the referral date. The PersonIDs can appear multiple times with different referral and assessment dates.

I'd like to be able to match the nearest assessment date to the referral date for each Person ID.

I've been able to work it out for assessment dates on or before using MAX(IF and an array formula looking at dates equal to or before the referral date (see below), but it doesn't work when I change it to MIN(IF and an array formula looking at assessment dates equal to or after the referral date.

=MAX(IF(IF(PersonIDRange=PersonID,AssessmentDates,"")<=ReferralDate,AssessmentDates,"")))

Any help around this would be hugely welcomed.

Thanks,

Chris

 

[Kaper]

how about something like:

=MIN(IF(ABS(IF(PersonIDRange=PersonID,AssessmentDates,date(9999,1,1))-ReferralDate)=MIN(ABS(IF(PersonIDRange=PersonID,AssessmentDates,date(9999,1,1))-ReferralDate)),AssessmentDates,date(9999,1,1)))

I hope I've not made mistakes but general idea is to build a set with time difference between (assesmentdates or 99/01/01(if wrong person) ) and referral dates.
Then each element of this set is compared to minimum of this set.
If it is smallest one, then AssesmentDAte is returned if not 99/01/01 .
And finally smallest element (the oldest assesment date with close referral date) will be selected

 

[strevs]

how about something like:

=MIN(IF(ABS(IF(PersonIDRange=PersonID,AssessmentDates,date(9999,1,1))-ReferralDate)=MIN(ABS(IF(PersonIDRange=PersonID,AssessmentDates,date(9999,1,1))-ReferralDate)),AssessmentDates,date(9999,1,1)))

I hope I've not made mistakes but general idea is to build a set with time difference between (assesmentdates or 99/01/01(if wrong person) ) and referral dates.
Then each element of this set is compared to minimum of this set.
If it is smallest one, then AssesmentDAte is returned if not 99/01/01 .
And finally smallest element (the oldest assesment date with close referral date) will be selected

Thanks Kaper - this is very helpful.

Chris