Multiple lookup & table population

So, I have attempted multiple IF, VLOOKUP, MATCH, INDEX formulas & arrays and still cannot crack how to do what I want excel to do. So, I built out a sample of the full spreadsheet and added notes as to what I am trying, but failing, to do. Any gurus able to take a look and work through what formula or array is needed is GREATLY appreciated! Please see attached. Thanks!

You have multiple instances of job on the jobs tab. Is that correct? If so, explain the logic of how employees are supposed to be matched?

Originally Posted by GlennUK

You have multiple instances of job on the jobs tab. Is that correct? If so, explain the logic of how employees are supposed to be matched?

Yes. The logic is:
"Match results" populates Job, Job Date, Emp ID, & Name based on "Emp choices" tab and matching the Name of the person with the highest "days" to a job matching their 5 preferences, until a match is made, and then continue to match each name to a job accordingly. In essence, first priority is a person's "days" aka... how long they've been certified & waiting for a job match. But, in the case where Name 9 can only do job EX300, they should be matched to an EX300 before Name 5 is, since Name 5 fits multiple job options. So, it gets tricky. Does that make sense?

Here's an example:
Name 1 & Name 10 have 120 days, but different #1 job choices. So, if a match to DX100 for Name 1 is possible, assign the DX100 job that is closest to their cert date. But, for Name 10, there is no EX400 available. So, they would get the next DX100 that is after their cert date.

I've attached a screenshot showing conditional formatting that is highlighting all the jobs that currently match the jobs tab. So, hope it helps to visualize the way the "match results" tab should be examining the data.

I've also attached a screenshot manually showing what the formula/array should output, if calculated correctly.

Let me know if I can clarify anything further. Thanks!

The logic is very convoluted, so trying to get a set of formulas to replicate it is very difficult. I failed to match your output ... there are 2 methods I created here, which you may be able to tweak for your own purposes. See attached:

Thanks, Glenn. Yes, it's very convoluted; it's ruining my brain! I created a new example and maybe this sheet will help? I am still unable to get it to execute, as needed

Originally Posted by JeteMc

The following fills the Sorted Data table:
For the Line # column use: =MATCH(R3,NAME,0)
For the Days Since Certified column use: =LARGE($D$3:$D$11,ROWS(A$1:A1))
For the Emp Name column use: =IFERROR(INDEX(NAME,AGGREGATE(15,6,(ROW(NAME)-ROW(B$2))/(CERT=Q3),COUNTIFS(Q$3:Q3,Q3))),"")
For the Emp ID column use: =INDEX(EMPID,MATCH(R3,NAME,0))
For the Matching Jobs column use: =INDEX(N$3:N$11,MATCH(R3,NAME,0))
I'll give the Matches table more thought and come back if I get an inspiration (unless someone else has solved before then).
Let us know if you have any questions.

All those work great; thanks! Yes, the "MATCHES" table is the part that is so frustrating. I know the logic is possible but I can't figure out the necessary "if this, then this, but not this" particulars that it needs. Standing by for any excel gurus to crack this!

JeteMc, your formulas have been added and an updated file is posted below. Now, just one last (intense) formula to crack!

Originally Posted by JeteMc

Here are two options.
The first (see sheet 1) uses the Sort table adding helper columns, which may be moved and/or hidden for aesthetic purposes, to the Matches table.
Row 1 of the helper columns (AD:AP) is populated using: =IFERROR(INDEX($R3:$R11,COLUMNS($A1:A1)),"")
Rows 3 and down are populated using: =MAX(0,IFERROR(COUNTIFS(INDEX($E$3:$M$11,MATCH(AD$1,NAME,0),0),$W3),0)-SUM($AC3:AC3)-SUM(AD$2:AD2))
On the Matches table the Emp Name column is populated using: =INDEX(AD$1:AP$1,MATCH(1,AD3:AP3,0))
The Emp ID column is populated using: =INDEX(EMPID,MATCH(AA3,NAME,0))

The second option (see sheet 2) would be to use the Excel sort feature and sort the Raw Data table first by column C and then by column N
The formulas for the Matches table and helper columns stay the same except that Emp ID is replaced with A3:A11 and Name is replaced with B3:B11.
Let us know if you have any questions.

JeteMc,

Nice work! However, there is still an issue present. In both examples, names are being matched to jobs that have job dates earlier than the employee's cert date. As in, a job being assigned with a job date (aka, start date) but the person claiming the job isn't yet certified. For example, on matches on Sheet1, Name 3 has a 04/01/2020 job but isn't certified until 06/01/2020.

So, to correct this, your formula would also need to include a verification that looks at the employee's "days since cert" and sees if it is greater than or equal to "job date counter" of specific job being looked at. Hope that makes sense. BTW, you are a huge help and can't believe how close you have come to cracking this! Thanks!!

Thanks, again, JeteMc! Worked great!