Nested IF Functions

Good Morning

I'm working on automating one of my Project reports.

Currently I calculate a Projects Managers Utilisation using a Pivot Table and then manually work out their allocations to provide an overall % of their time for each project and then sum them.

Where I'm struggling is I've written a large Nested IF function (which I feel is rather over complicated) though when It moves from one Project Band to another it won't carry over correctly.

My formula description is Project Band*Project Stage% = Utilisation

I've got the Project Band letter A in cell A4 then I ask excel if cell A4 matches the correct band to then calculate the overall weighting formula for each row below. Cell A4 is absolute as I want the A projects to calculate using that cell. I feel like it needs to be relative because I want this one formula to work if i drag it down though if it's relative it doesn't pick up the project band after cell A4.

Project Band B Projects start in Cell A25 and then the original formula doesn't work if A4 is relative but if Absolute it still doesnt work because it's still pulling from the A projects cell.

Is there a way I can associate the cells below A4 as A projects until they get to B projects in cell A25? or a way to adapt the current formula to apply to the B projects from A25?

I'll include the formulas I've used to calculate A projects.

=IF($A$4=$K$4,IF(A4=$L$11,$M$4*$M$11,IF(A4=$L$12,$M$4*$M$12,IF(A4=$L$13,$M$4*$M$13,IF(A4=$L$14,$M$4*$M$14,IF(A4=$L$15,$M$4*$M$15,IF(A4=$L$16,$M$4*$M$16,IF($A$4=$K$4,IF(A4=$L$11,$M$5*$M$11,IF(A4=$L$12,$M$5*$M$12,IF(A4=$L$13,$M$5*$M$13,IF(A4=$L$14,$M$5*$M$14,IF(A4=$L$15,$M$5*$M$15,IF(A4=$L$16,$M$5*$M$16,IF($A$4=$K$4,IF(A4=$L$11,$M$6*$M$11,IF(A4=$L$12,$M$6*$M$12,IF(A4=$L$13,$M$6*$M$13,IF(A4=$L$14,$M$6*$M$14,IF(A4=$L$15,$M$6*$M$15,IF(A4=$L$16,$M$6*$M$16,IF($A$4=$K$4,IF(A4=$L$11,$M$7*$M$11,IF(A4=$L$12,$M$7*$M$12,IF(A4=$L$13,$M$7*$M$13,IF(A4=$L$14,$M$7*$M$14,IF(A4=$L$15,$M$7*$M$15,IF(A4=$L$16,$M$7*$M$16,0))))))*B4,IF($A$4=$K$4,IF(A4=$L$11,$M$5*$M$11,IF(A4=$L$12,$M$5*$M$12,IF(A4=$L$13,$M$5*$M$13,IF(A4=$L$14,$M$5*$M$14,IF(A4=$L$15,$M$5*$M$15,IF(A4=$L$16,$M$5*$M$16,0))))))*B4,IF($A$4=$K$4,IF(A4=$L$11,$M$6*$M$11,IF(A4=$L$12,$M$6*$M$12,IF(A4=$L$13,$M$6*$M$13,IF(A4=$L$14,$M$6*$M$14,IF(A4=$L$15,$M$6*$M$15,IF(A4=$L$16,$M$6*$M$16,0))))))*B4,IF($A$4=$K$4,IF(A4=$L$11,$M$7*$M$11,IF(A4=$L$12,$M$7*$M$12,IF(A4=$L$13,$M$7*$M$13,IF(A4=$L$14,$M$7*$M$14,IF(A4=$L$15,$M$7*$M$15,IF(A4=$L$16,$M$7*$M$16,0))))))*B4,0)))))))))))))))))))))))))


Also is there a way I can raise a question to STL including a document as i find explaining this clearly quite difficult without. I'd ensure the document is GDPR compliant.

Alternatively I can be reached seperately on d.potts@opustech.co.uk

Thank you