Hi All
I have two tabs

The first tab, has a list of Usernames, and the approval groups they sit in
Some users sit in one approval groups, some users sit in many.
Some approval groups have one approver, some have many

The second tab, has a list of cost centres, and the approval group they sit in
All cost centres have one approval group, but many cost centres sit in each approval group


I start with a cost centre, and need to return all of the relative approver names
A simple lookup on the cost centre tab, using the approval group, returns only one name, when there might be 10

My current workaround is the following
I have pivoted both tabs to a third tab,
I filter the cost centre pivot for my required cost centre, to return the approver group
I then filter the approver group pivot using the approver group I have found on the first pivot, this returns me the names I need

But as a process, its a pain.


The system is unable to generate a combined list, detailing all of the duplicates.
There are 1200 cost centres, so creating this manually is out of the question.