Matching closest date with two sets of data

[Strevs81]

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. Unfortunately I can't upload files..

Thanks,

Chris

 

[Deepak]

Can we have a sample file. You may share cloud link as well as email me & i will upload it here. imdkbj at outlook dot com

 

[Strevs81]

Can we have a sample file. You may share cloud link as well as email me & i will upload it here. imdkbj at outlook dot com

Thanks a lot Deepak - I've just emailed you.

 

[Deepak]

I have uploaded the same in very 1st post.

 

[Khalid NGO]

Hi Chris,
Good day and Welcome to the forum

in C3:
=MAX(IF(($G$3:$G$13=A3)*($H$3:$H$13<=B3),$H$3:$H$13))
in D3:
=MIN(IF(($G$3:$G$13=A3)*($H$3:$H$13>=B3),$H$3:$H$13))
Both are array, CSE.

If you have 2010+, you can use these non array, with just enter:
in C3:
=AGGREGATE(14,6,1/($G$3:$G$13=A3)/($H$3:$H$13<=B3)*$H$3:$H$13,1)

in D3:
=AGGREGATE(15,6,1/($G$3:$G$13=A3)/($H$3:$H$13>=B3)*$H$3:$H$13,1)

Copy down...

Regards,

 

[Strevs81]

Thanks so much Khalid - that's so helpful. They both work exactly as I need them to. One question though, do you happen to know if using the second one is more efficient in terms of file size and memory use? The files I'm working with are around 30,000 and 45,000 rows respectively, so the smaller the better if that makes sense.

Thanks so much again.

Chris

 

[Deepak]

Hi,

Not always smaller is memory efficient. I think both will slow down the process for such a big range.

You can check the performance in a copy file.

I would suggest VBA for such big tasks.

 

[GraH - Guido]

One question though, do you happen to know if using the second one is more efficient in terms of file size and memory use?
Chris

Aggregate should be faster as it is built to support array.
But, like Deepak said, if using large range it could also become slow.
As alternative on VBA macro and array formulas you can also consider Power Query (if available for you).
Uploaded a PQ solution: add new data in the blue data tables, and refresh the green result table.