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]

Find more posts tagged with

Accepted answers

All comments

Mike TV

@mccoy_FSI

=IF(AND(Type@row="A", Limit@row=1), 1, IF(AND(Type@row="A", Limit@row=2), 3, IF(Type@row="B", "NA")))

mccoy_FSI

Thanks, I don't get an uparseable or other error but every cell in the column is empty.

Mike TV

@mccoy_FSI

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")))

mccoy_FSI

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:

Mike TV

@mccoy_FSI

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.

mccoy_FSI

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!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.