Using Multiple 'OR' statements in Google Sheets 'IF' formula with a Continuous Range with #ERROR

1
votes

I am working with a data set of ~7k rows in Google Sheets, but only 8 columns (A:H). I am trying to use an IF formula on a continuous range in Column B that checks several possible OR values from Column B that then returns a 'Yes' or 'No' value for each row in Column H. I keep getting a Formula Parse Error, but can't figure out where the problem is.

See below for my current formula that's not working. Thanks in advance for any ideas and suggestions.

=ArrayFormula(IF(OR(B5:B="AB Special Use 1 - Standard",
        B5:B="AB Special Use 2",
        B5:B="Design District Review",
        B5:B="Incremental Review",
        B5:B="Infrastructure Construction",
        B5:B="Infrastructure Construction Substantial Change",
        B5:B="Rezoning",
        B5:B="Special Use 2"
        B5:B="Substantial Change",
        B5:B="Substantial Change - Site Plan Zoning",
        B5:B="Substantial Change - Special Use"),
                "Yes", "No"))
1
google-sheets
"Carlos M" below has offered a solution. However, in looking at your formula, I can't help but feel that there is probably a more concise way of going about this. However, I'd need to know the entire list of possible options that might appear in Column B. If you'd like to share a link to the sheet (or a copy of it) and indicate the entire list somehow, I may be able to condense this formula for you. Just be sure to set the link's Share permission (when you create the link) to "Anyone with the link..." and "Editor." – Erik Tyler

1 Answers

2
votes

Solution:

The OR function does not work inside ARRAYFORMULA, but you can emulate the function using boolean addition.

=ArrayFormula(IF((B5:B="AB Special Use 1 - Standard")
        +(B5:B="AB Special Use 2")
        +(B5:B="Design District Review")
        +(B5:B="Incremental Review")
        +(B5:B="Infrastructure Construction")
        +(B5:B="Infrastructure Construction Substantial Change")
        +(B5:B="Rezoning")
        +(B5:B="Special Use 2")
        +(B5:B="Substantial Change")
        +(B5:B="Substantial Change - Site Plan Zoning")
        +(B5:B="Substantial Change - Special Use"),
                "Yes", "No"))