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

S

Swissmiss

New Member
Joined
Sep 27, 2013
Messages
23
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:

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

Outcome Sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Student Name[/TD]
[TD]Student ID[/TD]
[TD]Points[/TD]
[TD]Points Possible[/TD]
[TD]Mastery[/TD]
[TD]Outcome Name[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]fr4568dj[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]Not Mastered[/TD]
[TD]E3.13.D[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]fr4568dj[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Not Mastered[/TD]
[TD]E3.17.B[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]fr4568dj[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]Mastered[/TD]
[TD]E3.18[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]fr4568dj[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Not Mastered[/TD]
[TD]E3.19[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes

Book1
ABCDEF
1NameStudent IDPointsPoints PossibleMasteryOutcome Name
2John Doefr4568dj24Not MasteredE3.13.D
3John Doefr4568dj14Not MasteredE3.17.B
4John Doefr4568dj34MasteredE3.18
5John Doefr4568dj12Not MasteredE3.19
Outcome



Book1
ABCDEFGH
1Student NameStudent IDCourseTeacherE3.13.DE3.17.BE3.18E3.19
2John Doefr4568djEnglish IIISmith2131
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))
 
Upvote 0
Aladin Akyurek said:
ABCDJM
NameStudent IDPointsPoints PossibleMasteryOutcome Name
John Doefr4568djNot MasteredE3.13.D
John Doefr4568djNot MasteredE3.17.B
John Doefr4568djMasteredE3.18
John Doefr4568djNot MasteredE3.19
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
Sally Doe5554250Not MasteredE3.17.B
Sally Doe5554250MasteredE3.18
Sally Doe5554250Not MasteredE3.19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

</tbody></table>[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD]Mastered[/TD]
[TD]E3.13.D[/TD]
[/TR]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

</tbody>
Outcome​

ABCDATAUAVAW
Student NameStudent IDCourseTeacherE3.13.DE3.17.BE3.18E3.19
John Doefr4568djEnglish IIISmith

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"] Not Mastered [/TD]
[TD="align: right"] Not Mastered [/TD]
[TD="align: right"] Mastered [/TD]
[TD="align: right"] Not Mastered [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Sally Doe[/TD]
[TD="align: right"]5554250[/TD]
[TD="align: right"]English III[/TD]
[TD="align: right"]Jones[/TD]
[TD="align: right"] Mastered [/TD]
[TD="align: right"] Not Mastered [/TD]
[TD="align: right"] Mastered [/TD]
[TD="align: right"] Not Mastered [/TD]

</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))
Click to expand...


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 :)
 
Upvote 0
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 :smile:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]AT[/TD]
[TD]AU[/TD]
[TD]AV[/TD]
[TD]AW[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student Name[/TD]
[TD]Student ID[/TD]
[TD]Course[/TD]
[TD]Teacher[/TD]
[TD]E3.13.D[/TD]
[TD]E3.17B[/TD]
[TD]E3.18[/TD]
[TD]E3.19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe[/TD]
[TD]12345[/TD]
[TD]English III[/TD]
[TD]Smith[/TD]
[TD]Not Mastered[/TD]
[TD]Not Mastered[/TD]
[TD]Mastered[/TD]
[TD]Not Mastered[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sally Doe[/TD]
[TD]12345[/TD]
[TD]English III[/TD]
[TD]Smith[/TD]
[TD]Mastered[/TD]
[TD]Not Mastered[/TD]
[TD]Mastered[/TD]
[TD]Not Mastered[/TD]
[/TR]
</tbody>[/TABLE]
Master


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]J[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student Name[/TD]
[TD]Student ID[/TD]
[TD]Points[/TD]
[TD]Points Possible[/TD]
[TD]Mastery[/TD]
[TD]Outcome Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe[/TD]
[TD]12345[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]Not Mastered[/TD]
[TD]E3.13.D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]John Doe[/TD]
[TD]12345[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Not Mastered[/TD]
[TD]E3.17B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John Doe[/TD]
[TD]12345[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]Mastered[/TD]
[TD]E3.18[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]John Doe[/TD]
[TD]12345[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Not Mastered[/TD]
[TD]E3.19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sally Doe[/TD]
[TD]54321[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]Mastered[/TD]
[TD]E3.13.D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sally Doe[/TD]
[TD]54321[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]Not Mastered[/TD]
[TD]E3.17B[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Sally Doe[/TD]
[TD]54321[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]Mastered[/TD]
[TD]E3.18[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sally Doe[/TD]
[TD]54321[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Not Mastered[/TD]
[TD]E3.19[/TD]
[/TR]
</tbody>[/TABLE]

Outcomes
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Create mails with table - standard header specific row
Replies
1
Views
1K
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Question Question
Filter dataset with multiple parameters
Replies
3
Views
4K
JLGWhiz
JLGWhiz
M
  • Question Question
Please help!! Need help with specialized formula.
Replies
3
Views
972
Eric W
Eric W
W
  • Question Question
Converting a Hexadecimal with a length of 14 to Hexadecimal
Replies
6
Views
2K
joeu2004
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top