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

Name	Pretest Q1	Pretest Q2	Pretest Q3	Post Test Q1	Post Test Q2	Posttest Q3
john	A	B	C
Bob	B	C	D

<tbody>
</tbody>

My second spreadsheet

Name	Question	Answer
Bob	Q1	A
Bob	Q2	B
Bob	Q3	D
Sam	Q1	A
Sam	Q2	B
Sam	Q3	C
John	Q1	D
John	Q2	C
John	Q3	D

<tbody>
</tbody>

 

[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)