IFS Function Examples – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023

This tutorial demonstrates how to use the IFS Function in Excel and Google Sheets.

IFS Main Function

What is the IFS Function?

IFS is a “conditional” function. You define a series of logical tests, each with a return value associated with it. Excel works through each of your tests in turn, and as soon as it finds one that evaluates to TRUE, it returns the value you associated with that test.

IFS works much the same was as when nesting multiple IF statements, but IFS tends to be easier to read and work with.

How to Use the IFS Function

You use the Excel IFS Function like this:

=IFS(C3<50,"F", C3<60,"E", C3<70, "D", C3<80,"C", C3<90,"B", C3<=100, "A")

How to use IFS

This formula takes a student’s test score and converts it into their grade for that test.

The formula may look complicated, but it makes more sense if you put each test on a separate line:

C3<70, "D",
C3<=100, "A"

That’s better! It’s much clearer now that we have a series of tests paired with a return value. Excel works down the list until it gets a match, and then returns the grade that we’ve paired with that score.

You can define up to 127 tests in the IFS Function.

IFS – Default Value

With the normal IF statement, we define two values that Excel can return: one for when the logical test is true, and another for when it is false.

IFS doesn’t have the option to add a false return value, and if Excel doesn’t find a match in any of your tests, it will return #N/A. Not good, but, we can get around this by setting a default value.

It works like this:

C3<70, "D",
C3<=100, "A"
TRUE, “Unknown”

Default Value

We’ve set up a final logical test – just “TRUE”, by itself.

TRUE, naturally, evaluates to TRUE. So, if Excel doesn’t find a match in our previous tests, the final test will always be triggered, and Excel will return “Unknown.”

Return Values

Above we told IFS to return a text string – the grade associated with each test score. But you can also return numbers, or even other formulas.

For example, if you have a sales team, and you pay them commission based on the value of each sale, you could use IFS to calculate their commission according to the sale value.

C3<60000, C3*0.04,
C3 >=60000, C3*0.05,

Return Formula

Now IFS will check the value in C3, and then return the associated formula when it finds a match. So sales below $20,000 earn 2% interest, below $40,000 earns 3%, and so on.

For good measure, we’ve also set a default value of 0.

IFS in Google Sheets

The IFS Function works exactly the same in Google Sheets as in Excel:

IFS Google Function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions