# Create an array formula to obtain the average of an array of index and match function

1. ## Create an array formula to obtain the average of an array of index and match function

Good day Everyone!! I had a challenging problem in getting the average, Max & min of an array of data extract from a pool of table using index and match function.

Currently i am stuffing too much of index and match function in my formula to pull out individual data to manually get the result.

Can anyone advise me how i can create an array formula to get data for E11 to E13 without so many repetition as shown below?

You can image the nightmare when there is an increase of data (Sample data layout) require to extract and obtain the average, max & min

Thank you =AVERAGE(INDEX(\$D\$2:\$O\$5,MATCH(\$D\$31,\$B\$2:\$B\$185,0), MATCH(E10,\$D\$1:\$O\$1,0)), INDEX(\$D\$2:\$O\$5,MATCH(\$D\$31,\$B\$2:\$B\$185,0), MATCH(E11,\$D\$1:\$O\$1,0)),INDEX(\$D\$2:\$O\$5,MATCH(\$D\$31,\$B\$2:\$B\$185,0), MATCH(E12,\$D\$1:\$LK\$1,0)), INDEX(\$D\$2:\$O\$5,MATCH(\$D\$31,\$B\$2:\$B\$185,0), MATCH(E13,\$D\$1:\$O\$1,0)))  Register To Reply

2. ## Re: Create an array formula to obtain the average of an array of index and match function

Hello
If you're willing to reference the cells E10:G13, then try the following array formula (Ctrl+Shift+Enter)for the A1-D1 values:

Formula:  `Please Login or Register  to view this content.`

Same for MIN and MAX. Adjust the red range for the other criteria A3-D3 etc.

DBY  Register To Reply

3. ## Re: Create an array formula to obtain the average of an array of index and match function Originally Posted by DBY Hello
If you're willing to reference the cells E10:G13, then try the following array formula (Ctrl+Shift+Enter)for the A1-D1 values:

Formula:  `Please Login or Register  to view this content.`

Same for MIN and MAX. Adjust the red range for the other criteria A3-D3 etc.

DBY
Hello DBY.

You saved my day. Your solution is ingenious!! Thank you very much  Register To Reply

4. ## Re: Create an array formula to obtain the average of an array of index and match function

Hi DBY

If I want to obtain average for E10:G13 instead of E10:E 13, how should I do it as I received a error saying it is too large after I substitute E10:E13 with E10:G13

Thanks  Register To Reply