Formula to combine two worksheets

[vray0512]

I have 2 worksheets I need to combine - I need the data from the 2nd sheet that matches the first sheet to be put in the Post-test Q1, etc. So Sam from the second sheet would be ignored, Bob's data from 2nd sheet would be put into 1st sheet in appropriate place. Trying to match pre and post test data.
1st one has data like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Pretest Q1[/TD]
[TD]Pretest Q2[/TD]
[TD]Pretest Q3[/TD]
[TD]Post Test Q1[/TD]
[TD]Post Test Q2[/TD]
[TD]Posttest Q3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My second spreadsheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Q1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Q2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Q3[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]Q1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]Q2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]Q3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Q1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Q2[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Q3[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ajamess]

On the second sheet create a column where you do a concatenation of Name and Question.

For example, Assuming columns shown are A-C, in d2 type "=concatenate(a2,b2)"

Then fill down.

On the first Sheet, in E3, type "=index('Second Sheet'!$c:$c, match(concatenate($a3,e$2), 'Second Sheet'!$d:$d,0))

(Replacing "Second Sheet" with the name of the actual sheet)