How to use the IFS formula in Google Sheets

by Mar 17, 2017

The IF formula in Google Sheets helps us make a decision using a simple if-then-else construct. But, what if we need to go over multiple sets of criteria? Nesting IF formulas, you might think. Not wrong! Fortunately, we have a less cumbersome function to deal with that – the IFS formula. This takes in a set of expression and value pairs, evaluates them in sequence, and returns the first value whose corresponding expression is TRUE. If no expression evaluates to TRUE, it returns #N/A! error. The following flow chart explains the process.

Flow Chart Explaining IFS Formula Mechanism

Syntax

IFS(expression1, value1, [expression2, value2], …)

  • expression1 – the first logical expression which Google Sheets evaluates to either TRUE or FALSE
  • value1 – the value that IFS formula returns if the corresponding expression1evaluates to TRUE
  • expression2 – the next logical expression that evaluates to either TRUE or FALSE if the expression1 evaluates to FALSE
  • value2 – the value to return if expression2 evaluates to TRUE

Please note that only the first expression and value pair is mandatory. The subsequent pairs are optional. Also, it is noteworthy that neither the Google Sheets shows any contextual help for IFS formula, nor there is any available documentation. Nevertheless, it works just fine as described in this article.

Usage: IFS formula

Let us start with the fundamental version of the formula, and understand its working.

Fundamental Version of IFS Formula

The formula starts evaluating the first expression, in this case, 1>100, which is obviously FALSE. So, it moves on to the next expression, 1<100, which is TRUE. Hence it returns the value “1 is less than 100”. What happens, if all the expressions in the IFS formula evaluate to FALSE? Let’s see.

IFS Formula Example in Google Sheets

As we can observe, the age group for ages beyond 60 is not defined. And since Google Sheets has evaluated all the expressions to FALSE, it has no value to return. Hence we see the #N/A! error. It has no placeholder for a default value, but we can work around it by including an additional expression at the end and hard coding it to TRUE. This example is shown in the snapshot below.

IFS Formula in Google Sheets

If we were to execute the above-mentioned logic using only IF formulas, that would entail creating 5th level nested IF statements, which can get really long and complex. So, needless to say, the IFS formula is a great alternative to the otherwise complex nested IF statements.

Check our blog post How to use the AVERAGEIFS formula in Google Sheets to learn about the AVERAGEIFS formula in Google Sheets.

Share This