# Help with formula to return nth values based on multiple criteria

1. ## 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!

2. ## Re: Help with formula to return nth values based on multiple criteria

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))),"")

3. ## Re: Help with formula to return nth values based on multiple criteria

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.

4. ## Re: Help with formula to return nth values based on multiple criteria

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1