Return a Contact from another Sheet based on value


I have a source sheet (Sheet A) that maintains a list of projects and assignments.

I need a second request sheet (Sheet B) to intake requests. On this sheet, the project name will be included. I would like to use a formula to output the name of the assigned staff member for that project (found in sheet A). (*the sheets are more robust than this, but for the sake of what I am trying to accomplish I have simplified*)

I've used an Index / Collect formula, but it seems to return the value in Sheet B as simple text, and does not provide it as a Smartsheet Contact. Both columns in Sheet A and B are of the Contact List type.

Is there another approach that can help retain the Contact List format when I calculate the value in Sheet B?

Sheet A:

  • Project Column (A)
  • Assigned Staff (A)

Sheet B:

  • Project Column (B)
  • Assigned Staff (B)

Current formula: =INDEX(COLLECT({Assigned Staff (A)}, {Project Column (A)}, [Project Column (B)]@row), 1)


  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    @Chris Walker

    Try an index match instead.

    =INDEX({Assigned Staff (A)},MATCH([Project Column (B)]@row,{Project Column (A)},0))

  • Chris Walker
    Chris Walker ✭✭✭✭✭✭

    Thanks for the quick reply, Leibel!

    This approach also returns a value, but it appears the outcome is the same as I had before - returning the name as text, and does not have the typical functionality. I've tried testing by adding an alert workflow to notify {Assigned Staff (B)} when a change in the row; and an alert/email is not sending.

    I'm wondering if it is not possible to do a lookup on a Contacts List Type column and retain the functionality (i.e., user info/email, etc.).

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    Worked for me multiple times...

Help Article Resources