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

Oct 31, 2022 in Others 421 views

## 1 answer to this question.

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!

• 51,240 points

## INDEX MATCH formula in Excel returning incorrect results in some cells and correct results in others

Step through the formula that produces the ...READ MORE

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

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

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

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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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