INDEX, DISTINCT, and MATCH formulas

WilliamPaschall

I am trying to put together a list of our project managers and which business units each of them are responsible for. I need it to be in a certain format because right next to each of the business units, I am going to have 3 COUNTIFS formulas to count different things based on the project manager and business unit. I already have the COUNTIFS formulas working. I just need help indexing the business units based on the project manager. I am putting up to 10 business units per project manager. Most likely they will not have 10. The ultimate goal is to put this sheet in a report that will list the business units grouped by the project managers. Here is what I have so far with the formula and the results:

=INDEX(DISTINCT({Business Unit}), MATCH([Project Manager]@row, {Perk Partner}, [Row ID]@row), [Row ID]@row)

Any help with this will be greatly appreciated.

WilliamPaschall

Thanks @Paul Newcome. It took me a little bit to rework the formula but your suggestion got me what I was needing.

=IFERROR(INDEX(DISTINCT(COLLECT({Business Unit}, {Perk Partner}, [Project Manager]@row)), RowID@row), "")

WilliamPaschall

One thing I forgot to put in my original post was that I am trying to make this report as hands free as possible. This is the kind of report that does not need to have any maintenance after it is made.

Paul Newcome

Try an INDEX/DISTINCT/COLLECT instead.

WilliamPaschall

Thanks @Paul Newcome. It took me a little bit to rework the formula but your suggestion got me what I was needing.

=IFERROR(INDEX(DISTINCT(COLLECT({Business Unit}, {Perk Partner}, [Project Manager]@row)), RowID@row), "")

Paul Newcome

👍️Happy to help.