The Formula Gym
XP 0
Streak 0

The IFS function in Excel

IFS checks several conditions in order and returns the result for the first one that is true.

What IFS does

It replaces a long chain of IFs placed inside each other. You write pairs: a test, then the result to show when the test is true. Excel reads the pairs from left to right and stops at the first true test. To cover everything else, finish with TRUE as the last test and put your default result after it.

Syntax

=IFS(test1, result1, [test2, result2], ...)
test1
The first condition to check, such as B2>=70.
result1
What to return when test1 is true.
test2, result2, ...
Optional. More pairs, checked in order.

Worked example

ABC
1StudentScoreGrade
2Ana72A
3Ben48C
4Cy64B
5Di91A
6Eli55C
C2 =IFS(B2>=70,"A",B2>=60,"B",TRUE,"C") (copied down 5 rows)

Each score is tested against 70, then 60. TRUE at the end catches everything below, so no row is left without a result.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

HR

Turn review scores into rating bands.

Finance

Apply a different rate or fee for each spending tier.

Common mistakes

  • Forgetting the catch-all. If no test is true, IFS returns an #N/A error. End with TRUE and a default result.
  • Putting the tests in the wrong order. IFS stops at the first true test, so list the strictest one first.
  • Leaving a test without a result. Every test needs a result straight after it.

The IFS course

  1. 1
    Grade with IFSFree practice below
    Practise
  2. 2
    Four bands with IFSMembers lesson
  3. 3
    Match text codesMembers lesson
  4. 4
    IFS with ANDMembers lesson
  5. 5
    Commission rates with IFSMembers lesson

Common questions

When should I use IFS instead of nested IFs?

Use IFS when there are three or more outcomes. The pairs are easier to read and to change than IFs inside IFs.

Is IFS available in every version of Excel?

It was added in Excel 2019 and Microsoft 365. Older versions need nested IFs instead.

Related functions

Unlock every lesson

Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.

See membership