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 S
    Leibel S ✭✭✭✭✭✭

    @Chris Walker

    Try an index match instead.

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

  • 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 S
    Leibel S ✭✭✭✭✭✭

    Worked for me multiple times...

  • Hi Leibel,

    Thank you for your help on this. I was able to have the formula return a single contact. I'm looking to have this same function but return multiple contacts. Do you by chance have a formula for that? I was previously using a Join/Collect function but to Chris's problem that only returns simple text, which is not sending out notification. I'm looking to return multiple contacts and use those contacts for alerts/notification.

  • Leibel S
    Leibel S ✭✭✭✭✭✭


    There is no way to do that currently.

    you can only return a contact via index, however if that contact field is a MULTI contact then that would come in as well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!