Complex Task Management Array Formulas.

[DariusFilimon]

Hello, I have been able to find parts of what I need but couldn't make them work properly together. I am by no means an expert in Excel and tbh what I have so far is from internet. I frankly don`t understand more than half of what these formulas state but some of them work just as intended. This may be one of those things that cannot be done and if so, I would appreciate much if you help me realise that.
However, better would be if anyone can help with a solution.
I hope the explanations in the first sheet will suffice in showcasing the concept I'm trying to pull off.

Desired Outcome (everything has been edited manually to showcase)
First picture explains the concept and the second is a complete scenario.​

Where I got stuck. Please find the formulas bellow or download the excel doc.

COUNTIF($B$2:$E$5;H2)

{INDEX($H$2:$H$5;MATCH(1;SIGN(COUNTIF($K1:K$1;$H$2:$H$5)<SUMIF($H$2:$H$5;$H$2:$H$5;$I$2:$I$5));0))}

{INDEX($A$2:$A$5;MATCH(1;MMULT(--($B$2:$E$5=K2);TRANSPOSE(COLUMN($B$2:$E$5)^0));0))}

Can anyone make the last array formula return all the matching Tasks properly and integrate the percentage criteria in both arrays? Feel free to use helper columns. Ideally w/o VBA.

Thank you very much!

[#2 · Accepted solution]

@DariusFilimon As you already figured out, the formulae needed to achieve what you ask for are quite complicated. Very likely, somebody out here will be able to write the last one. Personally, I try to stay away from such formulae. They are not only difficult to write, but also difficult to maintain and adapt to a changing structure.

Excel has a wonderful tool called "Get&Transform Data" (a.k.a. Power Query). I've used your file and added a quick-and-dirty solution created with just Power Query. Not one single formula and no VBA needed. If, you have never used it, you may not understand a thing at first. But perhaps it will inspire you to learn something new. On the "Desired Outcome" tab, you'll find a green table. Feel free to change (add tasks and/or team members, change percentages) in the table at the top left (Table2). Right-click in the green table, select refresh and all will update in a fraction of a second. 

Now, if your aim is to learn complicated formulae, kindly ignore the Power Query solution. If you just want to get a job done efficiently and effectively, I'd suggest you learn Power Query.

[#3]

@Riny_van_EekelenA fenomenal solution. I will definitely look into this as I am more keen to learn tools and modules.

A thousand thanks!

[#4]

@DariusFilimon Come back here if you need more help!