Hi everyone. I've been racking my brain with this one for a while now and am hoping someone can help me to understand what change I need to make to return unique values. I have a workbook with two worksheets. One worksheet contains a list of every employee in my local organization, and in the next column has the supervisor name for each employee (looks like this):
<tbody>
</tbody>
<tbody>
</tbody>
This continues for up to 2000 rows.
On the other worksheet, I am trying to pull the employee list for each supervisor (to be used as a drop down later). My desired outcome would display the employee name for each matching supervisor (where the supervisor's name is provided in Row 1). It would look like this:
<tbody>
</tbody>
<tbody>
</tbody>
I've started with an index to match the supervisor name with the employee name, however, I can not for the life of me figure out how to use the subarray to only return unique values. Here's my formula that I'm entering into cells A2:C3 for the desired outcome above:
{=INDEX(TCS!$A$2:$A$2000,MATCH(A$1,TCS!$B$2:$B$2000,0))}
The formula works to pull an employee's name that matches the above supervisor name, however, it does not continue beyond the first employee that matches. Instead, I get a repeat of Employee 1 over and over again, instead of it finding the next employee that matches the Supervisor name. I know there should be a CountIF somewhere in here to make this work, but can't figure it out.
Any help would be appreciated
Thanks!
| Column A | Column B |
<tbody>
</tbody>
| Employee 1 | Supervisor 1 |
| Employee 2 | Supervisor 1 |
| Employee 3 | Supervisor 2 |
| Employee 4 | Supervisor 2 |
| Employee 5 | Supervisor 3 |
<tbody>
</tbody>
This continues for up to 2000 rows.
On the other worksheet, I am trying to pull the employee list for each supervisor (to be used as a drop down later). My desired outcome would display the employee name for each matching supervisor (where the supervisor's name is provided in Row 1). It would look like this:
| Column A | Column B | Column C |
<tbody>
</tbody>
| Supervisor 1 | Supervisor 2 | Supervisor 3 |
| Employee 1 | Employee 3 | Employee 5 |
| Employee 2 | Employee 4 |
<tbody>
</tbody>
I've started with an index to match the supervisor name with the employee name, however, I can not for the life of me figure out how to use the subarray to only return unique values. Here's my formula that I'm entering into cells A2:C3 for the desired outcome above:
{=INDEX(TCS!$A$2:$A$2000,MATCH(A$1,TCS!$B$2:$B$2000,0))}
The formula works to pull an employee's name that matches the above supervisor name, however, it does not continue beyond the first employee that matches. Instead, I get a repeat of Employee 1 over and over again, instead of it finding the next employee that matches the Supervisor name. I know there should be a CountIF somewhere in here to make this work, but can't figure it out.
Any help would be appreciated
Thanks!
