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.

• How to Correctly Use AND and OR Functions with IFS in Google Sheets
• How to Use IFS Function to Return an Array Result in Google Sheets