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.
IFS is a great function. I end it with catch-all … , True,”THERE IS NO MATCH”)