If statement referencing another sheet returns invalid operation
So I am using this:
=IF({Master Bookings SheetV2.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 SheetV2.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
Project Manager  Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
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 SheetV2.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
Project Manager  Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

Thank you so much Eric & Itai. It's working now
Help Article Resources
Categories
Check out the Formula Handbook template!