I have seen there are already a few questions on Stack that ask questions similarly but not in this particular way....

I am looking to for an Index match match that is able to return an array so I can average it.

In a perfect world I could use an index match match for concrete and 1/1/2024 and it would return me an array of 171 and 231 of which I could grab the average of those two....

I would like to stay away from VBA and because I have such a large data set would love a formula that is efficient. I have tried some other array formulas found here:

You will be a legend in the office if you can figure it out. It seems simple but looks can be deceiving.

Also the data can not be changed or manipulated. These two sit on different sheets.

DATA

I have seen there are already a few questions on Stack that ask questions similarly but not in this particular way....

I am looking to for an Index match match that is able to return an array so I can average it.

In a perfect world I could use an index match match for concrete and 1/1/2024 and it would return me an array of 171 and 231 of which I could grab the average of those two....

I would like to stay away from VBA and because I have such a large data set would love a formula that is efficient. I have tried some other array formulas found here: https://exceljet/formulas/return-array-with-index-function

You will be a legend in the office if you can figure it out. It seems simple but looks can be deceiving.

Also the data can not be changed or manipulated. These two sit on different sheets.

DATA

AVERAGE DATA

• arrays
• excel
• indexing
• excel-formula

• 1 Sumifs divided by countifs? – P.b Commented Mar 19 at 20:30
• There is an AVERAGEIFS function, as well. – tinazmu Commented Mar 19 at 22:08

1 Answer 1

Use FILTER instead for the column choice:

=AVERAGE(FILTER(INDEX(Sheet1!$A:$E,MATCH($A2,Sheet1!$A:$A,0),),Sheet1!$A$1:$E$1=B$1))

Sheet1

Sheet2