How to Test Several Conditions with IFS in Excel

Replace a tangle of nested IFs with one flat IFS formula that checks conditions in order and returns the first match.

You will learn to

• Write an IFS formula with several condition/value pairs
• Mix AND into an IFS test
• Use IFS to return numbers you can calculate with

How it works

IFS checks condition/value pairs from left to right and returns the value for the first condition that is TRUE: =IFS(B2>48,"Breached",B2>24,"At risk",TRUE,"On track"). It does the job of a nested IF without the pile of closing brackets, which makes long rule lists far easier to read and edit.

Two things trip people up. Order matters, so put the strictest test first. And IFS has no "else" argument: if no condition is TRUE it returns #N/A. Add TRUE as the final condition to give it a catch-all value.

=IFS(B2>48,"Breached",B2>24,"At risk",TRUE,"On track")