Help with formula to return nth values based on multiple criteria

Hey folks,

I know the title question has been answered a billion times, but I have a unique instance I can't figure out the logic on.

Basically, I have a large spreadsheet with a bunch of columns. For context, the spreadsheet contains details of what learners have completed what trainings (trainings are called "courses" on the spreadsheet), and each training is grouped by "Program". This means there can be multiple trainings in a single Program (e.g. Program 1 has 7 courses in it, Course 1, Course 2, etc.).

What I'm trying to do is give managers a clear understanding of which learners are trained on an entire program (meaning they've completed ALL courses within a program) using a % (100% means all courses are complete). I have that part down. But where I'm struggling is trying to display a list of what remaining courses a user needs to complete to be at 100%. I tried putting together an array formula that would return the nth value of any incomplete course (i.e. any course marked as In Progress or Not Yet Started) based on a user's name, but I haven't been able to make that work.

I've attached an example spreadsheet with my attempt to solve this. I have the array formula that I tried to make work on Sheet1, cell D2.

If anyone has any ideas or suggestions as to how I can make this work, I'd love to hear it. All help is appreciated!

Originally Posted by Glenn Kennedy

In D2, an ordinary formula, copied down:

=IFERROR(INDEX(data!B:B,AGGREGATE(15,6,ROW(data!$B$2:$B$100)/((data!$E$2:$E$100=$B$2)*(data!$G$2:$G$100<>"Completed")),ROWS($1:1))),"")

Worked like a charm, thank you very very much.