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

Frank S.

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?

Genevieve P.

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

Genevieve P.

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.

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

Genevieve P.

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