If statement referencing another sheet returns invalid operation

So I am using this:
=IF({Master Bookings Sheet-V2.0 Range 1} = [Cutting Room Number]@row, "Unavailable", "Available")
Above is the sheet where I'm checking the value of "Cutting Room Number" from another sheet called "Master Bookings Sheet V2.0". I have to check: if the value in "Cutting Room Number" in the above sheet is mentioned in the referenced sheet then "Unavailable" else "Available".
It's supposed to be a simple IF but it returns #INVALID OPERATION
Any help is appreciated.
Best Answers
-
@pdv90 Your formula isn't working because you are comparing an array to a single cell. You can try the following
=IF(CONTAINS([Cutting Room Number]@row, {Master Bookings Sheet-V2.0 Range 1}), "Unavailable", "Available")
-
Hey @pdv90,
I think you should try and use COUNTIF within the IF so then if the COUNTIF returns anything above 0 it will show as Unavailable.
The reason the formula is not working is that IF doesnt know how to compare a range to a cell.
You can also use Vlookup a similar way within the IF.
Let me know if you need help with writing the formula.
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Answers
-
@pdv90 Your formula isn't working because you are comparing an array to a single cell. You can try the following
=IF(CONTAINS([Cutting Room Number]@row, {Master Bookings Sheet-V2.0 Range 1}), "Unavailable", "Available")
-
Hey @pdv90,
I think you should try and use COUNTIF within the IF so then if the COUNTIF returns anything above 0 it will show as Unavailable.
The reason the formula is not working is that IF doesnt know how to compare a range to a cell.
You can also use Vlookup a similar way within the IF.
Let me know if you need help with writing the formula.
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Thank you so much Eric & Itai. It's working now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 515 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!