Index Match based on another column

[mark hansen]

I have a data set where the first two columns are cohort and person. This data will build line by line with additional cohorts and new people. I want to make dynamic charts to evaluate the beginning test score against the ending test score. We will be selecting the cohort to look at, and need the people (max 12 per group), and their scores to first populate a grid then we will make the chart from the grid.

I've been using index match to create (and build) a unique list of cohorts to select from using this formula:

{=IFERROR(INDEX(CohortList,MATCH(0,COUNTIF($D$6:D6,CohortList),0)),"")}

But I need help with getting the people for the selected group. I was thinking it would also be a Index match sort of formula to get the person's name that will go to the chart, but I don't need a unique list of names for the people, I need the list of people for the group.

The data starts at AB34 with the group name, and the person name is starting at AC34. I don't know how long the data set will be, but 1000 rows is a safe maximum.

Thanks for any help.
Mark

 

[mark hansen]

I wanted to follow up, with the solution I came up with. I'm not sure its the best solution, but it does work. after much trial and error, and Google, I came up with the following formula:

{=IFERROR(INDEX('EOP Data'!$AC$34:$AC$4700,MATCH(0,IF($AB$34='EOP Data'!$AB$34:$AB$4700,COUNTIF($AB$35:$AB35,'EOP Data'!$AC$34:$AC$4700),""),0)),"")}

The data set I'm using has the columns headers starting on AB33 and I suspect line 4700 will last about 6 years, that's why I used those ranges

'EOP Data'!$AC$34:$AC$4700 is the columns People
'EOP Data'!$AB$34:$AB$4700 is the column of groups
$AB$35:$AB35 is the cell above where chart list of people start, note the $
$AB$34 is the selectable group where the formula pulls just the people that were in that group.

This formula is placed in the first cell in the column for the chart labels and entered with CRTL+SHIFT+ENTER and dragged down to take care of the maximum number of people in the group (12 in my case).

Hope this helps somebody.
Mark