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.

Find more posts tagged with

Accepted answers

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.

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.