Insert Value based on matching name in a specific matching header column

[Swissmiss]

I am working on combining test data into one giant workbook. I have two sheets of data. I need to add the mastery values per standard for each student to my master sheet. What formula can I use to accomplish this?

Any help much appreciated

Master Sheet:

Student Name	Student ID	Course	Teacher	<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>E3.13.D	<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>E3.17.B	<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>E3.18	<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>E3.19
John Doe	fr4568dj	English III	Smith

<tbody>
</tbody>

Outcome Sheet:

Student Name	Student ID	Points	Points Possible	Mastery	Outcome Name
John Doe	fr4568dj	2	4	Not Mastered	E3.13.D
John Doe	fr4568dj	1	4	Not Mastered	E3.17.B
John Doe	fr4568dj	3	4	Mastered	E3.18
John Doe	fr4568dj	1	2	Not Mastered	E3.19

<tbody>
</tbody>

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Name	Student ID	Points	Points Possible	Mastery	Outcome Name
2	John Doe	fr4568dj	2	4	Not Mastered	E3.13.D
3	John Doe	fr4568dj	1	4	Not Mastered	E3.17.B
4	John Doe	fr4568dj	3	4	Mastered	E3.18
5	John Doe	fr4568dj	1	2	Not Mastered	E3.19
Outcome

Book1
	A	B	C	D	E	F	G	H
1	Student Name	Student ID	Course	Teacher	E3.13.D	E3.17.B	E3.18	E3.19
2	John Doe	fr4568dj	English III	Smith	2	1	3	1
3
Master

In E2 control+shift+enter, not just enter, and copy across:

=INDEX(Outcome!$C$2:$C$5,MATCH($B2,IF(Outcome!$F$2:$F$5=E$1,Outcome!$B$2:$B$5),0))

 

[Swissmiss]

	A	B	C	D	J	M
1	Name	Student ID	Points	Points Possible	Mastery	Outcome Name
2	John Doe	fr4568dj	2	4	Not Mastered	E3.13.D
3	John Doe	fr4568dj	1	4	Not Mastered	E3.17.B
4	John Doe	fr4568dj	3	4	Mastered	E3.18
5	John Doe	fr4568dj	1	2	Not Mastered	E3.19
6	Sally Doe	<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width=""><tbody>[TR] [TD="align: right"]5554250
7	Sally Doe	5554250	2	4	Not Mastered	E3.17.B
8	Sally Doe	5554250	4	4	Mastered	E3.18
9	Sally Doe	5554250	1	2	Not Mastered	E3.19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody></table>[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD]Mastered[/TD]
[TD]E3.13.D[/TD]
[/TR]

</tbody>

Outcome​

	A	B	C	D	AT	AU	AV	AW
1	Student Name	Student ID	Course	Teacher	E3.13.D	E3.17.B	E3.18	E3.19
2	John Doe	fr4568dj	English III	Smith	Not Mastered	Not Mastered	Mastered	Not Mastered
3	Sally Doe	5554250	English III	Jones	Mastered	Not Mastered	Mastered	Not Mastered

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Master​

In E2 control+shift+enter, not just enter, and copy across:

=INDEX(Outcome!$C$2:$C$5,MATCH($B2,IF(Outcome!$F$2:$F$5=E$1,Outcome!$B$2:$B$5),0))

I super appreciate your answer! I tried to apply it and tweak it to the actual cell names (I had a ton of columns) but I can't make it work. I added the real column cells at the top of your example. And in the cells I'm trying to get the word mastered, not mastered. The data is also for about 500 students that come in the same pattern so I added another student so you could see what I mean. What would be the formula to get it to look like this? Super grateful for any suggestions

 

[Swissmiss]

Okay I realized it didn't look right when I tried to edit yours in the reply so I retyped it below:

I super appreciate your answer! I tried to apply it and tweak it to the actual cell names (I had a ton of columns) but I can't make it work. I added the real column cells at the top of your example. And in the cells I'm trying to get the word mastered, not mastered. The data is also for about 500 students that come in the same pattern so I added another student so you could see what I mean. What would be the formula to get it to look like this? Super grateful for any suggestions

	A	B	C	D	AT	AU	AV	AW
1	Student Name	Student ID	Course	Teacher	E3.13.D	E3.17B	E3.18	E3.19
2	John Doe	12345	English III	Smith	Not Mastered	Not Mastered	Mastered	Not Mastered
3	Sally Doe	12345	English III	Smith	Mastered	Not Mastered	Mastered	Not Mastered

<tbody>
</tbody>

Master

	A	B	C	D	J	M
1	Student Name	Student ID	Points	Points Possible	Mastery	Outcome Name
2	John Doe	12345	2	4	Not Mastered	E3.13.D
3	John Doe	12345	1	4	Not Mastered	E3.17B
4	John Doe	12345	3	4	Mastered	E3.18
5	John Doe	12345	1	2	Not Mastered	E3.19
6	Sally Doe	54321	4	4	Mastered	E3.13.D
7	Sally Doe	54321	2	4	Not Mastered	E3.17B
8	Sally Doe	54321	4	4	Mastered	E3.18
9	Sally Doe	54321	1	2	Not Mastered	E3.19

<tbody>
</tbody>

Outcomes