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
-
You are pretty close there with the second one:
=IF(CONTAINS([Sheet A specific column]@row, {Sheet B Range 1}), "no", "NA")
Answers
-
You are pretty close there with the second one:
=IF(CONTAINS([Sheet A specific column]@row, {Sheet B Range 1}), "no", "NA")
-
You are AWESOME! Thanks for taking the time to help me out!
-
Help Article Resources
Categories
Check out the Formula Handbook template!
