If statement referencing another sheet returns invalid operation

pdv90

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.

Eric Law

@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")

Itai Perez

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.

Eric Law

@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")

Itai Perez

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.

pdv90

Thank you so much Eric & Itai. It's working now