Index collect formula question

Enoch Mak

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?

Kelly Moore

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

Enoch Mak

@Kelly Moore CONTAIN function worked for me thank you!

Enoch Mak

@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.

Kelly Moore

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

Enoch Mak

@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), "")

Enoch Mak

@Kelly Moore sorry doubled up on the question there.

I am seeing the error "invalid value"

Kelly Moore

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.