Similar to AND, we won’t be able to use the OR logical function to return expanded results in the usual way in Google Sheets. So what’s the solution to this?

In this quick Google Sheets tutorial, I’m sharing the workaround solutions.

We usually use the OR logical function within the IF to test whether any given arguments are logically TRUE and return a value accordingly.

But do you know how to use OR logical in an Array in Google Sheets?

I’m talking about the OR logical function expanded results in Google Sheets by using the ArrayFormula where we will replace cell reference A1 with an array like A1:A.

Example:

=if(or(A1="Saturday",A1="Sunday"),"Weekend","Workweek")

The below array formula won’t return results in multiple rows.

=ArrayFormula(if(or(A1:A="Saturday",A1:A="Sunday"),"Weekend","Workweek"))

Let’s learn how to use the OR logical function to return an expanded array result in Google Sheets.

The OR Logical to Return an Expanded Result in Google Sheets

There are 2-3 methods that you can follow to get expanded results when using the OR logical test within the IF function.

Here are them.

1. Using Nested IF.
2. Using the + Operator.
3. Using a Lambda Helper Function.

When I say expanded results, I mean an array result. Then how to Use OR Logical in Array in Google Sheets?

1. OR Logical Expanded Result Using Nested IF

Let me start with an example by converting a non-array OR formula in Google Sheets.

Similar: IFS and Nested IF in Google Sheets.

I have a few numbers in columns A and B. Let’s start applying OR logical tests by nesting IF formulas.

For example, if the value in cells A2 or B2 is greater than 0, I want the nested IF formula to return TRUE in cell G2, else FALSE.

Non-Array OR: =or(A2>0,B2>0)

This nested IF formula does that job!

=if(A2>0,TRUE,IF(B2>0,TRUE))

I want to do this OR function-related logical test in several rows. Of course, I can copy the formula down.

But, I want a single formula that does the logical test in every row and populates an expanded array result. Here you go!

=ArrayFormula(if(A2:A4>0,TRUE,IF(B2:B4>0,TRUE,FALSE)))

What about the example given at the beginning, which is weekend and workweek tests?

This will do!

=ArrayFormula(if(A1:A="","",if(A1:A="Saturday","Weekend",if(A1:A="Sunday","Weekend","Workday"))))

I don’t recommend this nested IF way of doing OR logical test in Google Sheets.

It is because if more columns or arguments are involved, the formula will become very tough to code.

2. Alternative to Nested IF for OR Array Expanded Result

Here is another way of using OR logical to return an expanded array result in Google Sheets. Here we will replace the OR function with the addition (+) operator.

=ArrayFormula(if((A2:A4>0)+(B2:B4>0)>0,TRUE,FALSE))

I’ve only applied the formula in cell F2 but the result expanded to the rows below. Here, I should explain this formula usage.

Formula Breakup:

Formula:

=A2>0

Result: TRUE

Formula:

=B2>0

Result: TRUE

Formula:

=(A2> 0) + (B2> 0)

Result: 2

I hope this makes sense.

To test whether A1:A contains weekends or workweeks, use the following formula.

=ArrayFormula(if(A1:A="",,if((A1:A="Saturday")+(A1:A="Sunday"),"Weekend","Workweek")))

3. OR Array Formula Using MAP Lambda – Recommended

From the above example, you can understand that we can’t use OR for expanded array results in Google Sheets. But wait!

Now, there is a new method using the MAP function.

We can expand =or(A2>0,B2>0) using the MAP as follows.

=map(A2:A4,B2:B4,lambda(a,b,or(a>0,b>0)))

So, we can use the following shortest formula to test whether A1:A contains weekends or workweeks.

=map(A1:A,lambda(a, if(a="",,if(or(a="Saturday",a="Sunday"),"Weekend","Workweek"))))

That’s all. Thanks for the stay. Enjoy!