Index/Match Question, Display Time of meeting based upon the date

Frank S.
Frank S. ✭✭✭✭✭✭
edited 10/19/21 in Formulas and Functions

Here is my problem:

I have 2 standing meeting slots, one on the morning and one in the afternoon. A meeting can be scheduled during the morning, during the afternoon, both, or none.

Here is my need:

I wanted to show if there is a meeting in either slot on a given day. Right now I can show if one meeting slot is filled but when I try and fill the 2nd meeting slot is does not update.

My progress:

For the am meeting column:

=IFERROR(IF(CONTAINS("am", INDEX({Meeting Time}, MATCH([Meeting Date]@row, {Meeting Dates}, 0))), INDEX({Meeting Time}, MATCH([Meeting Date]@row, {Meeting Dates}, 0)), ""), " ")

For the PM meeting Column:

=IFERROR(IF(CONTAINS("pm", INDEX({Meeting Time}, MATCH([Meeting Date]@row, {Meeting Dates}, 0))), INDEX({Meeting Time}, MATCH([Meeting Date]@row, {Meeting Dates}, 0)), ""), " ")

Both currently work for a single value. If a second 10/22 date is entered but 1-3pm is entered the column does not update.

What am I missing or where is my mistake?

Frank Smith, PMP

Assistant Director | IT Special Projects Mgr.

Oregon Parks & Recreation Department

If my response helps, please mark it as an accepted answer. 😎

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Frank.Smith

    The INDEX(MATCH is only finding the first Match, so if there are two values ("am" and "pm") it will find one of the two and the other formula will error (or be blank).

    Instead, try a JOIN(COLLECT function! You can add the criteria of "am" or "pm" directly into the COLLECT function, like so:

    =JOIN(COLLECT({Meeting Time}, {Meeting Dates}, [Meeting Date]@row, {Meeting Time}, CONTAINS("pm", @cell)), 1)


    Since the JOIN function will only bring back a value if there's a match, and otherwise is blank, there's no need for an IFERROR, either.

    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Frank.Smith

    The INDEX(MATCH is only finding the first Match, so if there are two values ("am" and "pm") it will find one of the two and the other formula will error (or be blank).

    Instead, try a JOIN(COLLECT function! You can add the criteria of "am" or "pm" directly into the COLLECT function, like so:

    =JOIN(COLLECT({Meeting Time}, {Meeting Dates}, [Meeting Date]@row, {Meeting Time}, CONTAINS("pm", @cell)), 1)


    Since the JOIN function will only bring back a value if there's a match, and otherwise is blank, there's no need for an IFERROR, either.

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    @Genevieve P.

    That worked great and thank you for explaining my error with Index/match.

    I knew I was missing something and appreciate you pointing me in the right direction.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem, I'm glad I could help. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!