Transpose column to rows based on values in two other columns

[jacobburdis]

I've got a data set of test-takers performance on thousands of items on a few different versions of a test.

Screen Shot 2017-05-11 at 1.50.28 AM.png

Column A = test_id (a unique value that indicates on a different spreadsheet which version of the test this was, taken by which student)
Column B = item_id (a non-unique value that repeats. This is the id assigned to each individual test question)
Column C = Syl Correct (basically, the score of the question)
Column D = column A with duplicates removed
I've laid out all of the test questions (all possible item_id's) in the remaining columns.

I need to transpose the scores in Column C for each unique test ID, but since not every question is on each test, I need to go cell by cell and check if the item_id matches a value in Column B, but only where Column A equals the value in cell D2. So that is the logic I need in words. I need to translate this into an excel formula.

My best guess is to use an IF formula, but I'm struggling figuring how to represent this logic test. Something like this:

=IF(E$2=the value in a cell in column B, but only for the range where Column A=$D2, $C2, "") I think "" means leave blank, but I could be wrong

Does anyone know how to represent this logic to run the if statement?

Here is the attachment to the spreadsheet
https://www.excelforum.com/attachmen...1&d=1494520552

[Special-K]

Can you attach the workbook otherwise anyone that wants to solve this has to reproduce test date and we'll all be typing in the same thing?
Remove/rename any sensitive data if necessary.

You might be able to get away with an INDEX(MATCH(IF...))

[jacobburdis]

Thanks, I added the attachment.

https://www.excelforum.com/attachmen...1&d=1494520552

[jacobburdis]

Actually, after a lot more digging, i figured it out.

{=IFERROR(INDEX($C$2:$C$33,MATCH(1,($D2=$A$2:$A$33)*(E$1=$B$2:$B$33),0)),"")}

And then just drag across all columns