Practice free

IFS function

IFS checks conditions in order and returns the result paired with the first one that is true. It replaces a chain of nested IFs, and returns #N/A if nothing is true.

Example

Read it as a sentence: check whether the Qty in D2:D7 is more than 10 and show big if so, and if not show small, which starts with big.

Practice this in the app

Syntax

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Arguments in [square brackets] are optional.

logical_test1 (required)
First condition.
value_if_true1 (required)
Result if the first condition is TRUE.
logical_test2, value_if_true2 (optional)
More condition and result pairs, up to 127.

Returns: The result paired with the first TRUE condition.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=IFS(FALSE,1,TRUE,2) 2 Second test
=IFS(5>3,"a") a One pair
=IFS(1,"one") one Number test
=IFS(TRUE,"a",1/0,"b") a Error in later test
=IFS(Items!D2:D7>10,"big",TRUE,"small") big Array tests spill
=IFS(Places!E3<1,"Walk",Places!E3<5,"Cab",TRUE,"Train") Cab Site: featured, Scotland Yard 2.8 miles
=IFS(FALSE,1) #N/A No test true
=IFS("a",1) #VALUE! Text test

The Items sheet these formulas read

The Places sheet these formulas read

Watch out for

  • If nothing is TRUE you get #N/A; end with TRUE, "default" as a catch-all.
  • Conditions are checked in order, so put the most specific first.
  • Arguments must come in pairs or you get an error.

Where it works

Excel
IFS arrived in Excel 2019. Older versions do not have it.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .