MATCH function question/problem (multi-criteria)

[Brian.Crawford]

I have 2 WS one with "time-entry" data (days worked) the other with "work-completed" (1 unit of work per row). I must match the 400,000 "work-completed" to 33,000 "time-entry records" ie Link/associate the "work-completed" to "time-entry" by DATE (there are multiple "work-completed" records per day per employee).
.
They both have a "project-type" and "employee-ID" in common (which I have already combined as a single key) but the date in the "work-completed" WS is not necessarily an exact match to the dates in the "time-entry" records. It is an approximate match where the "work-completed" date could be a few days later (or more) than the "time entry" date (but would always be greater than the time entry date if they do not match).
.
I have tried various match formulas, including making a single key including the date(serial), sorting the "time-entry" data with both MATCH types (1, -1), etc. I've also tried array formula but don't appear to be able to make exact match (for "project-type" & "employee-ID") at the same time as the required approx. match for the date.
.
Give the volume of data this needs to be relatively fast as even a simple match can take 5-10 minutes (on a very high-end machine).

All advice appreciated
Thanks
Brian

 

[Brian.Crawford]

Updated: need to add some clarity at the end of the 2nd paragraph.

I have 2 WS one with "time-entry" data (days worked) the other with "work-completed" (1 unit of work per row). I must match the 400,000 "work-completed" to 33,000 "time-entry records" ie Link/associate the "work-completed" to "time-entry" by DATE (there are multiple "work-completed" records per day per employee).
.
They both have a "project-type" and "employee-ID" in common (which I have already combined as a single key) but the date in the "work-completed" WS is not necessarily an exact match to the dates in the "time-entry" records. It is an approximate match where the "work-completed" date could be a few days later (or more) than the "time entry" date (but would always be greater than the time entry date if they do not match). Effectively I need to then back-date the “work-completed” record to the date of the most closely related (ie closest previously dated) “time-entry” record that matches the “project-type” & “employee-ID".
.
I have tried various match formulas, including making a single key including the date(serial), sorting the "time-entry" data with both MATCH types (1, -1), etc. I've also tried array formula but don't appear to be able to make exact match (for "project-type" & "employee-ID") at the same time as the required approx. match for the date.
.
Give the volume of data this needs to be relatively fast as even a simple match can take 5-10 minutes (on a very high-end machine).

All advice appreciated
Thanks
Brian