Averageifs dynamic row and column

[Evendis]

Hi, I'm looking for help for an Averageifs on a dynamic row and columns.

[Sam Capricci]

for Agent (K5)... =IF($N$2="Sale",AVERAGEIFS($F:$F,$C:$C,$J5,$D:$D,K$4),IF($N$2="call",AVERAGEIFS($G:$G,$C:$C,$J5,$D:$D,K$4),AVERAGEIFS($H:$H,$C:$C,$J5,$D:$D,K$4)))
for Manager (K6)...
=IF($N$2="Sale",AVERAGEIFS($F:$F,$B:$B,$J6,$D:$D,K$4),IF($N$2="call",AVERAGEIFS($G:$G,$B:$B,$J6,$D:$D,K$4),AVERAGEIFS($H:$H,$B:$B,$J6,$D:$D,K$4)))
for Team (K7)...
=IF($N$2="Sale",AVERAGEIFS($F:$F,$A:$A,$J7,$D:$D,K$4),IF($N$2="call",AVERAGEIFS($G:$G,$A:$A,$J7,$D:$D,K$4),AVERAGEIFS($H:$H,$A:$A,$J7,$D:$D,K$4)))
drag all right for week 45

[AliGW]

Are you still using Excel 2013?

[Glenn Kennedy]

or, with a minor layout adjustment:

=AVERAGEIFS(INDEX($F$2:$H$500,,MATCH($O$2,$F$1:$H$1,0)),INDEX($A$2:$C$500,,IFERROR(MATCH($J5,$A$1:$C$1,0),1)),$K5,$D$2:$D$500,L$4)

copied across and down.

[Evendis]

Thank you for your replies.

I'm still trying to apply it to my work. For some reasons the results I get looks a little bit off.

Thank you again for your time.

[Evendis]

Update: I was confused by the results I got. The issue was coming from the data extract. My IT people resolved the issue. The formula works perfect.
Thank you so much again. I was struggling setting up this AverageIFS formula on my own. The setup of multiple INDEX MATCH within the formula was challenging me, but when I look at your formula, at the end, it makes perfect sense.

I'm sure I will come back another day asking for your help sometimes. Be assured that you made my life easier with your help.
Thank you.

Evendis.