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

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

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1