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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!