Vlookup or Index Match?


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


  • anaMG
    anaMG ✭✭✭

    Thank you Paul!

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!