google-sheets-formulaarray-formulas

OR function not working with arrayformula in Google Sheets

I want to use an arrayformula function to apply a formula across all rows in my sheet.

However, when adding the following formula:

=ARRAYFORMULA(IF(OR(E2:E="English",E2:E="Spanish"),E2:E,"English"))

I don't get the desired result. It only works if the fomrula is added to each row, but this isn't going to work for me since I import a sheet weekly and want arrayformulas to apply data.

Edit this public sheet

The expected outcomes is in Column F. But column G, where the arrayformula is, doesn't do that.

Solution

  • Within arrayformula(); use + for OR and * for AND

    So change this part of the formula

    OR(E2:E="English",E2:E="Spanish")

    to

    (E2:E="English")+(E2:E="Spanish")