If Index collect formula to capture the Meeting slot availability

Joseph Aloysias

Hi All,

I'm attempting to compare data from one sheet, which contains meeting bookings, with another sheet that lists all available meeting slots. My formula seems to work correctly when the condition is met ("True"), but it results in an "#INVALID VALUE" error when the condition is not met ("False").

Can someone assist me in resolving this issue with the formula?

=IF(INDEX(COLLECT({Meeting Intake - Meeting Room}, {Meeting Intake - Meeting Room}, @cell = [Meeting Room]@row, {Meeting Intake Date}, @cell = Date@row, {Meeting Intake Meeting Slot}, @cell = [Meeting Slots]@row), 1) = [Meeting Room]@row, "Booked", "Available")

Paul Newcome

What would you want the output to be in those instances?

Joseph Aloysias

Output should show whether that slot is "available" or "booked"

Paul Newcome

But how are we supposed to know whether it is available or booked if there is no match (if false)? Would you want it to show booked for when there is a match and available for when there is not?

Joseph Aloysias

Yes exactly.

Paul Newcome

Give this a try:

=IF(IFERROR(INDEX(COLLECT({Meeting Intake - Meeting Room}, {Meeting Intake - Meeting Room}, @cell = [Meeting Room]@row, {Meeting Intake Date}, @cell = Date@row, {Meeting Intake Meeting Slot}, @cell = [Meeting Slots]@row), 1), ".") = [Meeting Room]@row, "Booked", "Available")