How to use the IFS function in Google Sheets

The IF function in Google Sheets enables logical decision making with a simple if-else structure. It checks whether a condition in a cell is true or false. IFS supercharges this function and lets you check against multiple conditions at once.

You could achieve the same result by nesting IF functions but it can result in a very long formula. And this can be difficult to work with and lead to errors in your spreadsheet.

This is where IFS comes in handy. This formula takes a set of expression and value pairs, evaluates them, and returns the first value of an expression that’s TRUE.

This works exactly the same as the Excel IFS function, so if that’s your preferred software you can still follow along.

Spreadsheets are just the start

Syntax

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

  • expression1 – the first logical expression to be evaluated as TRUE or FALSE.
  • value1 – the value to be returned if expression1 is TRUE
  • expression2 – the next logical expression to be evaluated as TRUE or FALSE only if expression1 evaluates as FALSE
  • value2 – the value to be returned if expression2 is TRUE

Only the first expression and value pair is mandatory, while subsequent pairs are optional. Also, the result of the function will depend on the first TRUE expression and all subsequent expressions will be overlooked.

The flowchart below illustrates the logic of the IFS function.

IFS formula flow chart

    How to use the IFS function

    Let’s start with the simplest form of the function to make it easy to understand.

    =IFS(A2>100, A2 & ” is greater than 100″, A2<100, A2 & ” is less than 100″)

    The function evaluates the numbers 50 and 150 and returns whether they are less than or greater than 100.

    IFS FUNCTION GOOGLE SHEETS 1

    First, the function evaluates the first expression (50>100) which is obviously FALSE. So it moves on to the next expression (50<100) which is TRUE. Therefore it returns the value “50 is less than 100”.

    In row 3, it is doing the same thing, but the value is now 150. So when the function evaluates the first expression, it finds 150>100 to be TRUE. It then outputs the value “150 is greater than 100”, and does not evaluate the second expression.

    What happens if all the expressions in the IFS function evaluate to FALSE? Let’s take a look at this scenario with a different example. I have used the IFS function to create a formula for age in Google Sheets.

    =IFS(B2<10, “Kid”, B2<20, “Adolescent”, B2<30, “Young adult”, B2<60, “Middle aged”)

    IFS FUNCTION GOOGLE SHEETS 2

    The spreadsheet goes through each expression one by one and evaluates it to be TRUE or FALSE. If an expression is TRUE, it will output the value associated with that expression; if it is FALSE, the function moves on to the next expression.

    As you can observe, the age group for ages beyond 60 is not defined. And for Elliot, aged 65, Google Sheets has evaluated all the expressions as FALSE and has no value to return. Hence you see the #N/A! error.

    To avoid the error, you can add an expression that will evaluate as TRUE for Elliot.

    IFS-FUNCTION-GOOGLE-SHEETS-3

    Here, I’ve added a “Senior citizen” category that applies to Elliot. As you can see, the #N/A! error has been resolved.

    Comparison with nested IFS

    In the image below, there’s another column where the last function is built using nested IFS. This still works, but you can see the function is longer and involves more brackets making it confusing. This only gets worse if this is part of a larger formula.

    =IF(B2<10, “Kid”, IF(B2<20, “Adolescent”, IF(B2<30, “Young adult”, IF(B2<60, “Middle aged”,  IF(B2>=65, “Senior citizen”, “Adult”)))))

    So, needless to say, the IFS function is a great alternative to otherwise complex nested IF statements.

    IFS FUNCTION GOOGLE SHEETS 4

    Let’s look at another application of the IFS function that follows the same logic.

    IFS FUNCTION GOOGLE SHEETS 5

    This formula assigns students a letter grade based on a test score. The table on the right shows how letter grades correspond to test scores. While in the left table you can see the IFS function was used to assign the grades based on these ranges.

    =IFS(C3<50, $G$7, C3<65, $G$6, C3<75, $G$5, C3<85, $G$4, C3>84, $G$3)

    Thanks for reading!

    That’s how to get started with the IFS function. If you like this post, please share it with your network via the social media buttons on the left.

    If you’re looking for more Google Sheets tips and tricks, check out our post on how to use the AVERAGEIFS function and AVERAGEIF function or our other articles below.

    Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

    Ready to streamline your spreadsheet data?

    You may also like…