Pull Last Entry from One Sheet to Another Based on Criteria

I'm trying to figure out a formula to add the last or latest "Status Notes" entry from our Work Request Monthly Update sheet to the correct/matching Work Request's "Status Notes" on our Master Work Log Sheet.

Both sheet's Primary Column's are named Work Request Number and have mirroring numbers. The Monthly Update sheet receives updates monthly, so I'd like the last "Status Notes" for a specific Work Request Number to be copied into the "Status Notes" column on the Master Work Log Sheet.

Any thoughts?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    To match on two criteria, you would actually need to use an INDEX/COLLECT which would in turn require a helper column with an additional formula since you can't nest a COLLECT function inside of another COLLECT function.


    But...


    The way the MATCH function works when evaluating a single column is from top to bottom. Once it finds the first match, it stops looking and returns that row number. If you have new forms being populated a the top, then the most recent will be the one picked up by the MATCH function.


    Then we can stick with the INDEX/MATCH and only have to match on the work request number.

    =INDEX({Range to pull from}, MATC([Work Request Number]@row, {Other Sheet Work Request Number}, 0))

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!