AverageIF - array - averaging test results

[WorthJ]

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?

Thanks for your help,

J

 

[Fluff]

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

 

[Fluff]

Thanks for updating to your profile.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1	Response ID	Question 1	Question 2	Question 3		ID
2	44	3	4	5		45	5
3	45	6	7	2		47	5.666667
4	46	4	5	6		46	5
5	47	2	7	8		48	4.666667
6	48	2	5	7		44	4
Master

Cell Formulas
Range	Formula
G2:G6	G2	=AVERAGE(INDEX($B$2:$D$6,MATCH(F2,$A$2:$A$6,),))

 

[WorthJ]

Works an absolute treat, thanks

 

[Fluff]

You're welcome & thanks for the feedback.