If Index collect formula to capture the Meeting slot availability
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")
Answers
-
What would you want the output to be in those instances?
-
Output should show whether that slot is "available" or "booked"
-
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?
-
Yes exactly.
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!