How do you reference "any/all cells within a column" within an if/then formula?


I am using two sheets to validate against each other. Sheet A is my main sheet. I have all the data I need related to my project there. Sheet B has a list of places we cannot work with. I want to say that If a cell in sheet A is equal to anything in any of the cells within sheet B (which is only one column) then I want it to notify me.

I attempted to use an "if/then" formula to help, because I will not be the one using this sheet long term, and it will be passed on. I want minimal effort to maintain the two sheets.

Currently my equation is:

=IF([Sheet A specific column]@row = {Sheet B Range 1}, "no") however I'm getting the following error: "Invalid Operation"

I also tried another way with this equation:

=IF([Sheet A specific column]@row = CONTAINS({Sheet B Range 1}), "no", "NA")

and got the following error: "incorrect argument set"

The range is always just the entire column within sheet B

I want to avoid a vlookup, because that requires maintenance. Is there a way to state "all cells within x column" so I can use my initial equation?

Id appreciate any help!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!