How to pull a number from previous row into secondary column matching using a row identifier (name)


Above is the sheet I am working with. I am keeping track of a weekly score for each student and want to try and pull previous weeks scores into the current week row so I can track scores over the past month for a specific student. Currently the formula I am using to pull the previous week score into the column 'LastWk' is as follows:

=IFERROR(INDEX([Week Score]:[Week Score], MATCH(Student@row + "-" + (Week@row - 1), UniqueTag:UniqueTag, 0)), "")

As you can see, the result of this formula is a blank cell.. Not sure where to proceed since it doesn't seem like any of the typical error codes show up


  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 03/02/22

    What error do you get if you pull the IFERROR out of there?

    Do you have the week 1 data in the list somewhere? Because I don't see the result of MATCH(Student@row + "-" + (Week@row - 1) in this screen shot.

    This works for me, I am not sure why it is not working for you:

    INDEX([Week Score]:[Week Score], MATCH(student@row + "-" + (week@row - 1), UniqueTag:UniqueTag, 0))

  • Kishan Naik RA

    James thanks for the response and help! I found my own mistake, I had an issue with my Week 1 UniqueTag which was throwing off the formula. Taking the IFERROR out helped me catch that since the error I received once it was removed was NO MATCH so I knew something had to be wrong with the UniqueTag

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!