Vlookup or Index Match?

anaMG

Hi! I have 2 sheets:

• Master Sheet
• Test Results Sheet

Whenever a teacher inputs a mock test score in the "Test Results Sheet", I want that score to be copied to the "Master Sheet" in the same student's row. I could do this with this formula: =IFERROR(VLOOKUP([Student]@row, {Test Results Sheet}, 2, 0), " ")

Now, I'd like to pull the latest mock test result, since teachers input a form every time a student takes a mock test. How can I do this?

Thank you very much in advance!

Paul McGuinness

Hi @anaMG

Got this going with a couple of formulas, there may be a easier or cleaner way to make this work but this was the first way i thought of.

Based on your description you have something like this on your Test results sheet

So you want the 2nd score for student A to pull through on your Master sheet, normally we could use a helper column but in this case we can use a latest test date column on the Master Sheet.

The first formula finds us the latest test date for each student from the Test Results Sheet:

(Just to confirm the formula references relate to the entire columns on the Test Results Sheet)

=MAX(COLLECT({Date of Test}, {Student}, Student@row))

The second formula then uses that date to find the latest test score for each student

=INDEX(COLLECT({Test Results}, {Student}, Student@row, {Date of Test}, [Latest test date]@row), 1)

Hope this helps

Paul

anaMG

Thank you Paul!

I was wondering if there's a way of doing this with 1 formula only, any thoughts?

Thanks!