+ Reply to Thread
Results 1 to 4 of 4

Help with formula to return nth values based on multiple criteria

  1. #1
    Forum Contributor
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2013
    Posts
    131

    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!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,958

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



  3. #3
    Forum Contributor
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2013
    Posts
    131

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

    Quote Originally Posted by Glenn Kennedy View Post
    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,958

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return values based on multiple criteria with no duplicates
    By Ksun23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2018, 08:34 PM
  2. [SOLVED] Return total amount values based on multiple criteria
    By MyStix01 in forum Excel General
    Replies: 3
    Last Post: 10-12-2017, 09:05 AM
  3. [SOLVED] Return Values based upon multiple criteria
    By frustrated in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2016, 08:56 AM
  4. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  5. Replies: 2
    Last Post: 03-03-2014, 10:03 PM
  6. Array formula to return multiple values based on various (OR) criteria
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-20-2013, 05:12 AM
  7. Lookup/ Return and concatenate values based upon multiple criteria
    By steveboise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2013, 10:43 AM

Bookmarks

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