Average Index-Match with Multiple Matches

[jbiehl]

I am trying to average students' scores by standard.
In the example below, I need a formula to return the average of Tom's Standard "RL.2" Quiz Scores (cells B14 and C14) in the Standard Average for "RL.2" (cell H2).

I'm thinking it must include AverageIF-Index-Match, like below, but since the Index-Match only returns the first value, it's current form isn't working out.
=AVERAGEIF(A13:E16,INDEX(A13:E16,MATCH(G14:G16,A13:A16,0),MATCH("*RL.2*",A13:E13,0)))

*Note: this will be implemented on a much larger scale with hundreds of assignments, so Index-Match nested in the Small or Large formulas, where I have to copy the formula hundreds of times is not an option.

Thank in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
13		RL.2 Quiz 1	RL.2 Quiz 2	RL.3 Quiz 1	RL.3 Quiz 2			RL.2	RL.3
14	Sally	0	30	60	90		Tom	35	95
15	Joe	10	40	70	100		Sally	15	75
16	Tom	20	50	80	110		Joe	25	85
Data

Cell Formulas
Range	Formula
H14:I16	H14	=AVERAGE(FILTER(FILTER($B$14:$E$16,$A$14:$A$16=$G14),ISNUMBER(SEARCH(H$13&"*",$B$13:$E$13))))

 

[mikerickson]

If you put this in H1 and drag right and down
=AVERAGEIF($B$13:$E$13, H$13&"*", INDEX($B$14:$E$300, MATCH($G14, $A$14:$A$300, 0),0 )

 

[jbiehl]

Thanks so much to the both of you!
Both formulas worked!
Now I have a follow-up question...

I put the formula that averaged their quizzes in a new tab called, "Roll-up". There are separate tabs for various sources which provide the standard data. For example, A12:E16 above might be in a tab called "September_Gradebook" but there is also a "June_Gradebook".

Is there a way to tell Excel to automatically add late-start/new students to the end of 'Roll-Up'!A:A (student column), if they weren't in the initial roster, but then show up in another student column of a different tab, like, 'March_Gradebook'!A:A?

Thanks again!

 

[Fluff]

As that is a totally different question, you will need to start a new thread.

 

[jbiehl]

As that is a totally different question, you will need to start a new thread.

Thanks!

 

[Fluff]

You're welcome.