Using OR to Return Expanded Results in Google Sheets

Published on

The OR function doesn’t natively return expanded results when used with arrays in Google Sheets; instead, it returns a single Boolean value. You can use LAMBDA functions or the + operator (for logical OR) as an array-compatible alternative to achieve this.

The following formula will return TRUE or FALSE if any logical expressions are TRUE in the provided range. However, it won’t evaluate the arguments row by row or return expanded results:

=ArrayFormula((OR(B2:B12="Guava", B2:B12="Mango")))

To return expanded results, you can use one of the following formulas:

OR Alternative for Expanded Results Using Array-Compatible Operator

=ArrayFormula((B2:B12="Guava")+(B2:B12="Mango"))

This will return 0 (FALSE) or 1 (TRUE), expanding the results across the rows.

OR Alternative for Expanded Results Using Array-Compatible Operator

How Does This Work?

The formula consists of two logical expressions that return either TRUE or FALSE:

=ArrayFormula(B2:B12="Guava")
=ArrayFormula(B2:B12="Mango")
+ (Plus) Operator for OR Function

These expressions generate two arrays filled with TRUE or FALSE values. By adding these arrays together, we get:

  • 0 if both logical expressions evaluate to FALSE.
  • 1 if at least one of the logical expressions evaluates to TRUE.

In Google Sheets, TRUE is equivalent to 1, and FALSE is equivalent to 0, which allows us to perform this addition.

OR Function with MAP and LAMBDA for Expanded Array Results

=MAP(B2:B12, LAMBDA(value, OR(value="Guava", value="Mango")))

This will return FALSE or TRUE, expanding the results across the rows.

MAP Function for Expanded OR Function Results

Coding This Formula Step-by-Step

You can expand the native OR function row by row using the MAP and LAMBDA functions in Google Sheets.

For a single row, the OR logical formula will look like this:

=OR(B2="Guava", B2="Mango"))

You can convert this to an unnamed custom function using the LAMBDA function as follows:

LAMBDA(value, OR(value="Guava", value="Mango"))

Here, we have assigned the name value to cell B2 and used that within the formula.

We use the MAP function to apply this LAMBDA function to each value in the array B2:B12. It maps each value in the array and produces an array result.

Syntax: MAP(array1, [array2, …], lambda)

However, note that the MAP function can be resource-intensive. Therefore, to achieve expanded results using OR, it is generally more efficient to use the first option that employs the array-compatible operator.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.