Having problems with IF(AND) or maybe I need IF(OR)...? Not sure.
I need to return a value based on two columns' variables.
Basically, if it's type A in the Type column I need to return a 1 or a 3 in the Ref column based on what's in the limit column. Then if it is type B in the Type column, that row should always be NA in the Ref column.
So in the above table as an example:
row one should return 1 in [Ref]
row two and three should return NA in [Ref]
row four should return 3 in [Ref]
Answers
-
=IF(AND(Type@row="A", Limit@row=1), 1, IF(AND(Type@row="A", Limit@row=2), 3, IF(Type@row="B", "NA")))
-
Thanks, I don't get an uparseable or other error but every cell in the column is empty.
-
I just created a test sheet and my formula works just fine for me:
This leads me to believe your numbers aren't really numbers on your sheet. So try this then:
=IF(AND(Type@row = "A", VALUE(Limit@row) = 1), 1, IF(AND(Type@row = "A", VALUE(Limit@row) = 2), 3, IF(Type@row = "B", "NA")))
-
I really appreciate your kind assistance, but I still can't get it to work. I have to change the words for proprietary reason, but my table is basically exactly as below, not sure if this really changes anything in the formulas you already recommended. Requirements are below the table, again your help is truly appreciated:
-
It definitely wouldn't work with the 2nd formula because the Limitation column isn't numbers so VALUE won't work. You'd need to use:
=IF(AND(Vehicle@row="Boat", Limitation@row="C"), 1, IF(AND(Vehicle@row="Boat", Limitation@row="B3"), 3, IF(Vehicle@row="Car", "NA")))
Your problem I believe was that you probably weren't putting quotation marks around C and B3 which since they're not numbers you need to put quotes around them in the formula.
-
This is my fault, actually all the cells weren't blank, there are some variables I left out for the column IF checks, those are returning blank. So if I just add those I should also be able to get an NA or whatever I need. So thank you, I think this is solved!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!