Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Index and Match formula with a contact list

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))


image.png


Best Answer

  • Answer ✓

    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

  • Employee

    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

    Source Sheet.PNG

    Target Sheet with the Formula

     

    Target Sheet.PNG Target Sheet-Formula.png

    Current User as Jane Doe

     

    Current User- Jane Doe.PNG


    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

  • Answer ✓

    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!

Trending in Formulas and Functions