New User Question - how to reference Multi-Select dropdown column values in a formula

I am trying to develop a budget component for a project plan (using the SmartSheet portfolio management template).

In the project plan worksheet, a task can be assigned to multiple people with different cost rates. I have multi-select column called "Assigned To" and another column for "estimated task cost". How do I walk through the contents of a multi-select column to then look up their rate in another table. I have the lookup working when it is a single value just not sure how to parse the multi-select column.

Here is how I am looking up the staff rates in case there is a better way - still very much a SmartSheet beginner:

[Estimated Labor Hours]@row * VLOOKUP([Assigned To]@row, {Staff Tiers and Rates Range 3}, 5) - [Actual Labor Hours]@row * VLOOKUP([Assigned To]@row, {Staff Tiers and Rates Range 3}, 5)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference with sensitive/confidential data blocked?


    You are most likely going to end up using a SUMIFS, but I can't be sure until I see your structure.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!