Over-complex formulas are often the symptom of poor data structure
Some modellers treat complex formulas like a badge of honour.
But they are not a sign of your Excel expertise.
Quite the opposite in fact. You should always strive for the simplest possible solution, one that you can explain to a colleague in under 30 seconds.
In almost every model where I have found over complex formulas it has been driven by the structure of the data being modelled.
The great news is that within Excel you can use PowerQuery to help you transform your data into a structure that you can leverage.
In the video below I show one of the most powerful tools in PowerQuery: Unpivot.
An incredibly simple way to transform your data into a useable structure.