Return a Value When Expression is FALSE in IFS in Google Sheets

Published on

This tutorial explains how to return a value when all logical expressions (conditions) evaluate to FALSE in the IFS function in Google Sheets.

The IFS function evaluates multiple conditions and returns a value corresponding to the first TRUE condition. If all conditions are FALSE, it returns a #N/A error.

Syntax: IFS(condition1, value1, [condition2, …], [value2, …])

Unlike IFS, the IF function handles a single condition and can return values based on TRUE or FALSE, often requiring nested IF statements for multiple conditions.

Syntax: IF(logical_expression, value_if_true, value_if_false)

In IFS, you specify multiple conditions (logical_expression) and their corresponding values (value_if_true). However, IFS lacks a direct value_if_false option.

We can use a simple trick to make IFS return a specified value when all conditions are FALSE. Here’s an example.

How to Return a Value When the Logical Expression is FALSE in IFS

The following IFS formula will return “South America” if A1 is equal to “Brazil,” or “Asia” if A1 is equal to “Indonesia”:

=IFS(A1="Brazil", "South America", A1="Indonesia", "Asia")

If you change the value in cell A1 to “India,” the formula will return #N/A since no conditions match.

To return a different value instead of #N/A when none of the conditions match, you can use the following approach. For instance, you may want to return “Country is not specified.”

If you use nested IFs, you can specify it as follows:

=IF(A1="Brazil", "South America", IF(A1="Indonesia", "Asia", "Country is not specified"))

Here’s how to return a value when no conditions match in IFS:

Option 1: Using a Last Condition that Always Evaluates to TRUE

The following formula will return “Country is not specified” when the value in A1 is neither “Brazil” nor “Indonesia”:

=IFS(A1="Brazil", "South America", A1="Indonesia", "Asia", TRUE, "Country is not specified")

The last condition in this formula is TRUE, so the corresponding value will be returned.

You don’t have to use TRUE; you can use any condition that evaluates to TRUE, such as 1=1, 0=0, or 1<2.

This is one way to return a value when all conditions are evaluated as FALSE in the IFS function.

Option 2: Using IFNA to Handle FALSE Conditions in IFS

When all the logical expressions are evaluated to FALSE, IFS returns #N/A. You can replace this error with a custom value using the IFNA function as follows:

=IFNA(IFS(A1="Brazil", "South America", A1="Indonesia", "Asia"), "Country is not specified")

It’s up to you which option to choose. I recommend Option 1.

Resources

You may find the following IFS tutorial helpful for further advancing its use.

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...

1 COMMENT

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.