# AverageIF - array - averaging test results

#### WorthJ

##### New Member
Hello,

I'm trying to average a set of test results based on a lookup criteria.

The criteria is someone's ID number and i'm creating a separate sheet with everyone persons averaged results (there are 600 people).

What is the forumla for calculating an average from an array when using a look up?. I think it must be some kind of an array but I can't get it to work.

What I'm looking for is:

Average value for Questions 1, 2 and 3 (columns b,c,d) when Response ID (column A) = A cell reference of my choice (i.e. b8)

 a b c d 1 Response ID Question 1 Question 2 Question 3 2 44 3 4 5 3 45 6 7 2 4 46 4 5 6 5 47 2 7 8 6 48 2 5 7 7 8 Average mark across Q1, Q2 and Q3 44 What is the formula? 9 Average mark across Q1, Q2 and Q3 45 What is the formula?

J

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

#### Fluff

##### MrExcel MVP, Moderator
Thanks for updating to your profile.
+Fluff New.xlsm
ABCDEFG
1Response IDQuestion 1Question 2Question 3ID
244345455
345672475.666667
446456465
547278484.666667
648257444
Master
Cell Formulas
RangeFormula
G2:G6G2=AVERAGE(INDEX(\$B\$2:\$D\$6,MATCH(F2,\$A\$2:\$A\$6,),))

#### WorthJ

##### New Member
Works an absolute treat, thanks

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
14
Views
161
Replies
10
Views
94
Replies
4
Views
101
Replies
14
Views
147
Replies
7
Views
149