Find most recent updated status for category

Hi all,

I'm searching for a solution on following idea:

I have a sheet with following four columns:

Created (date) | Category (Dropdown) | Previous Status (RYG balls) | Current Status (RYG balls)

Now I want to auto-populate "Previous Status" with the last "Current Status" of the same "Category" when a new row is entered. One example:

2020-02-15 | A | Green | Yellow

2020-02-15 | B | Green | Green

2020-02-18 | A | Yellow | Red

When editing a new row and selecting Category = A I want to see "Red" in "Previous Status".

Is that possible somehow?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!