Index and Match formula with a contact list

Options

I'm trying to return the assigned colleague by using a match function between the project sheet & our source resourcing sheet. I've used this exact formula connecting two other sheets and it worked great. This time it is returning the correct value but it is NOT pulling it in as a contact which means the "current user" filter will not work. I've checked that the source sheet has the column set up as a contact list as does the column where I've placed the formula. Any ideas why this wouldn't be acknowledging the contact? All colleagues are part of our smartsheet account.

=INDEX({LD Resource Allocation}, MATCH(Course@row, {Course from Resource Allocation}, 0))



Best Answer

  • callylatchford
    Answer ✓
    Options

    Thanks for testing this out. I went through to pull screenshots for you & noticed the only discrepancy was that my source sheet had "allow multiple contacts per cell" selected while my target sheet did not. Checking that box fixed it, even though there was only one contact for what I was looking for.

Answers

  • Krissia B.
    Krissia B. Moderator
    Options

    Hello @callylatchford,

     

    Upon further review and test on this, I am unable to replicate the same issue you are experiencing. The formula you have is working as expected. It is giving me a contact type result which allow for use in a "Current User" filter. See my tests/images below for reference.

     

    Source Sheet

    Target Sheet with the Formula

     

    Current User as Jane Doe

     


    If the above guideline doesnt resolve your issue, please give additional details of the Source & Target sheets, with screenshots (please remove any sensitive data) and the way you have the contacts added in to help us further assist with this issue!

     

    Visit the links below in reference to this topic.

     

    Cheers,

    Krissia

  • callylatchford
    Answer ✓
    Options

    Thanks for testing this out. I went through to pull screenshots for you & noticed the only discrepancy was that my source sheet had "allow multiple contacts per cell" selected while my target sheet did not. Checking that box fixed it, even though there was only one contact for what I was looking for.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!