Excel Formula multiple Index Match and Average the result

Question

1. I have two index match formulas looking at another excel tab pivot data

INDEX(MATCH())+INDEX(MATCH())

1. Both formulas have IFERROR = 0

IFERROR(INDEX(MATCH()))+IFERROR(INDEX(MATCH()))

1. And overall an IFERROR to return 0

IFERROR(IFERROR(INDEX(MATCH()))+IFERROR(INDEX(MATCH())))

Above works OK.

I now need to average so if the first index match return 30 and the second index match return 20 I need (30+20)=50/2 = 25

If the first index match return 0 and the second index match return 40 I need (0+40)=40/1 = 40 and vice versa

Is there a way to do this that doesn't involve IF(AND(, can AVERAGEIF or IFS be used or something else to keep it simple?

Example of Data Table being used:

Formula

Thanks, Gridlock but your example is missing the last match in the top formula e.g. if you had this:

Put formula in cell G3 =INDEX($M$3:$O$8,MATCH(G$1&$D3,$L$3:$L$8,0),MATCH($F3,$M$2:$O$2,0)). 

Can someone please help me with this?

Answer 1

If the conditions are separate and unrelated, try this function:

=AVERAGE(SUMIF($L$3:$L$7,G1&F3&D3,$M$3:$M$7),SUMIF($L$3:$L$7,G1&F3&E3,$M$3:$M$7))

I'll use 2001 as a condition because I see that you have general yearly conditions, therefore let's combine it with a wildcard in average if:

=AVERAGEIF($L$3:$L$7,G1&"*",$M$3:$M$7)

Hope it works for you!