Can I populate a multi select contact list off of what is selected in a multi select drop down?

I would like to populate a multi select contact list in the Master Sheet below based on what is selected in a multi select drop down (Project Requirements). The Lookup Sheet on the right lists what should be populated based on what is selected.

It's only reading ONE project requirement. Any assistance would be greatly appreciate.

Thank you in advance,

Joyce

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/02/22

    There's only one way I've gotten this to work. It's not pretty, but it does what you want. You need to add to your lookup every combination of Requirement # values with their assigned resources:

    Row 7:

    Requirement 1 Joyce

    Requirement 2 Tom

    Requirement 3 Billy James

    Row 8:

    Requirement 4 Mary

    Requirement 5 Joyce

    Requirement 6 Billy

    Row 9:

    Requirement 2 Tom

    Requirement 4 Mary

    Requirement 6 Billy

    etc:

    Then use a standard INDEX/MATCH on your master sheet:

    =IFERROR(INDEX({Lookup Sample - IT Resource}, MATCH([Project Requirements]@row, {Lookup Sample - Requirements}, 0)), "")

    My testing:



    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!