Formula Help

Jason Cohen

Hi everyone-

I am not sure if this is possible. I am trying to figure out a way to pull in a date of a course completion based on identifying the employee, the course they took, and the date of completion of said course.

My source data sheet has this info with example data

Course Code Employee ID Date of Completion

"course 1" 12345 3/22/22

"course 1" 67890 3/22/22

"course 2" 12345 3/21/22

"course 3" 55555 3/21/22

My target sheet is setup with the following columns:

Employee ID Course 1 Course 2 Course 3 Course 4 Course 5

I want to be able to identify the date of completion from the source sheet and populate it under the right column that matches the appropriate course from the master list. The source data sheet will be updated regularly with new completions.

Is this possible even? Thank you!

Genevieve P.

Hi @Jason Cohen

Yes, this is possible!

The way I would do this is to use an INDEX(COLLECT formula. The COLLECT function will work as a sort of "filter" where you list each column in the source sheet and your criteria that you're looking for.

It may also be easiest if you duplicate the column name into the Top Row of the second sheet. This is so that you can reference the text in that cell, which has the course name, as the value to search for (then you don't need to change the formula in each column, you can just reference the cell in the top row).

Try something like this:

=INDEX(COLLECT({Date of Completion Column}, {Employee ID Column}, [Employee ID]@row, {Course Code Column}, [Course 1]$1), 1)

We listed the range to bring back:

=INDEX(COLLECT({Date of Completion Column},

Then the first criteria column and criteria:

{Employee ID Column}, [Employee ID]@row,

Then the second criteria column and criteria:

{Course Code Column}, [Course 1]$1

^ The column reference will change if you Drag-Fill the formula to the right, so in the second course column you should see: [Course 2]$1 instead.

We close off with , 1) for the INDEX function (it says to bring back the first match it finds).

Note that all of your "Course 1" "Course 2" columns in this sheet with the formula will need to be Date Type of columns in order to return a date.

Here are some Help Articles that may be useful: INDEX Function / COLLECT Function / Formulas: Reference Data from Other Sheets / Create a Cell or Column Reference in a Formula

Let me know if this makes sense and works for you!

Cheers,

Genevieve