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, 2022 in Others by Kithuzzz
• 38,010 points
2,602 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, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
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, 2022 in Others by narikkadan
• 63,420 points
1,003 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,420 points
385 views
0 votes
1 answer

Crawling through multiple excel files, match and copy data to master file

One application only. It would be quicker ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
356 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, 2022 in Others by Edureka
• 13,670 points
1,238 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, 2022 in Others by Edureka
• 13,670 points
1,426 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, 2022 in Others by Edureka
• 13,670 points
699 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,332 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
955 views
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, 2022 in Others by narikkadan
• 63,420 points
1,476 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