All 4 tests have the same 19 questions, but arranged in a different order.
You’ll find the Excel file below.
Leave your answer as a comment below.
Hints
This problem is challenging to set up.
It’svery easyto get confused.
Part of the solution involves carefully locking cell references.
If you have trouble with these kind of references, practice building themultiplication table shown here.
This problem requires carefully constructed cell references!
You might find yourself thinking you could do this faster manually.
Yes, for a small number of questions.
However, with more questions (imagine 100, 500, 1000 questions) the manual approach getsmuch harder.
Below, I explain both interpretations along with formulas that can be used with each.
Doesn’t get much simpler than this.
This is not the correct answer for this challenge, but it’s a nice example anyway.
Interpretation #2 (correct)
The second interpretation is more complicated.
C5:E23 is a key that tells you only where you might find a question from test A.
It is not reporting a question number, it is reporting an index of sorts.