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 collect formula question

Hello I need some assistance with my index collect function,

In one cell i'd have

=IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, Email:Email, "Person 1"), 1), "")

In another cell i'd have

=IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, Email:Email, "Person 2"), 1), "")

Currently the function's 1st criteria is working fine, however the 2nd criteria checking the "email:email" column isn't pull back the all the data i need. Whenever there are multiple emails it seems to ignore the cell all together.

Any way around this with other functions i could add?

Answers

  • Community Champion

    Hey @Enoch Maki

    Evaluating multiselect cells require the specific functions of HAS, CONTAIN or FIND. With a multiselect- Contact field use the HAS function.

    =IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, Email:Email, HAS(@cell, "Person 2")), 1), "")

    Will this work for you?
    Kelly

  • ✭✭✭

    @Kelly Moore CONTAIN function worked for me thank you!

  • ✭✭✭
    edited 11/14/24

    @Kelly Moore - facing a separate issue with the same formula now. I have to utilize the formula to reference another sheet after trying the HAS, CONTAIN, and FIND function it seems to now return blank instead.

    =IFERROR(INDEX(COLLECT({Location and Event}, {Week # Start Date}, [Week #]@row, {Assigned Resources}, Contains("Person 1", @cell)), 1), "")

    To add more context Assigned Resources column is a contacts column contains the name/email of person 1,2,3 which maybe the difference when my test sheet only listed them as texts.

  • Community Champion

    Hey @Enoch Mak

    Remove the IFERROR function so you can tell what error it is encountering. As a good practice, always write a formula first without the IFERROR so that you can ensure the formula works properly before masking any errors.

    Please let me know what the error says.

    Kelly

  • ✭✭✭

    @Hey @Kelly Moore thank you! Seems like only the contain function worked in this instance. I tired HAS but everything returned blank, then i tried FIND where it did not return anything with multiple emails within the email column, however CONTAIN worked!

    =IFERROR(INDEX(COLLECT([Location and Event]:[Location and Event], [Column23]:[Column23], [Week #]@row, Email:Email, Contain("Person 2 <Person 2@gmail.com",@cell)), 1), "")

  • ✭✭✭

    @Kelly Moore sorry doubled up on the question there.

    I am seeing the error "invalid value"

  • Community Champion

    Hey @Enoch Mak

    For your Location and Event collect - as a quick test (be sure to turn OFF Column Formulas if it is turned on before doing this) in one cell, remove range&criteria from the formula one by one. This will tell you which criteria is filtering your answer to zero and/or producing the error. Be sure the IFERROR is removed from the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions