Excel Formula multiple Index Match and Average the result

0 votes
  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:

enter image description here

Formula

enter image description here

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

enter image description here

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?

Oct 31 in Others by Kithuzzz
• 20,660 points
73 views

1 answer to this question.

0 votes

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))

enter image description here

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)

enter image description here

Hope it works for you!

answered Oct 31 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
36 views
0 votes
1 answer
0 votes
1 answer

Average TIME Calculation differs between Excel and PowerBI

They are calculating different things. In Power ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
323 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
391 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
240 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
71 views
0 votes
1 answer
0 votes
1 answer

Excel formula that combines MATCH, INDEX and OFFSET

Try this: =INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4, ...READ MORE

answered Nov 7 in Others by narikkadan
• 37,660 points
53 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP