Help on formula - Looking at two columns and depending on the responses in both

I have a sheet and want to create a formula that looks at the info in a column named "Bill Status" and column named "Our Stance". Based on the combinations of the responses in these columns I want and output of Win, Loss, Null.

Column named Bill Status, possible options are:
Active
Dead
Enacted

Column named Our Stance, possible options are:
Support
Oppose
Neutral

I need to have it result in Win, Loss, Null based on the following:
Support + Enacted = Win
Oppose + Dead = Win
Support + Dead = Loss
Oppose + Enacted = Loss
Support + Active = Null
Oppose + Active = Null
Neutral +Enacted = Null
Neutral + Dead = Null
Neutral + Active = Null

I have no idea where to start as everything I tried either results in INCORRECT ARGUMENT or UNPARSEABLE.

Find more posts tagged with

Formulas

Accepted answers

All comments

heyjay

=IF(OR(
AND([Our Stance]@row = "Support", [Bill Status]@row = "Enacted"), 
AND([Our Stance]@row = "Oppose", [Bill Status]@row = "Dead")), "Win", 

IF(OR(
AND([Our Stance]@row = "Support", [Bill Status]@row = "Dead"), 
AND([Our Stance]@row = "Oppose", [Bill Status]@row = "Enacted")), "Loss", 

IF(OR(
AND([Our Stance]@row = "Support", [Bill Status]@row = "Active"), 
AND([Our Stance]@row = "Oppose", [Bill Status]@row = "Active"), 
AND([Our Stance]@row = "Neutral", [Bill Status]@row = "Enacted"), 
AND([Our Stance]@row = "Neutral", [Bill Status]@row = "Dead"), 
AND([Our Stance]@row = "Neutral", [Bill Status]@row = "Active")), "Null", 

"")))

Nic Larsen

Try: =IF(OR(AND([Bill Status]@row = "Enacted", [Our Stance]@row = "Support"), AND([Bill Status]@row = "Dead", [Our Stance]@row = "Oppose")), "Win", IF(OR(AND([Bill Status]@row = "Dead", [Our Stance]@row = "Support"), AND([Bill Status]@row = "Enacted", [Our Stance]@row = "Oppose")), "Loss", "Null"))

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