IFS and SWITCH practice exercises

Choose between more than two outcomes without stacking IF inside IF: IFS for a list of tests, SWITCH for a list of exact values.

Start the first exercise

How IFS and SWITCH work

IFS checks a list of tests in order and returns the answer next to the first test that is true. You give it pairs of arguments: a test, then what to show when that test is true, repeated as often as you need. SWITCH is the sibling for exact values. It takes one value to look at, then pairs of a match and a result, and an optional last argument for anything else.

=IFS(B2>=90,"A",B2>=70,"B",TRUE,"C") gives A for 90 or more, B for 70 to 89 and C for everything else. =SWITCH(B2,"N","North","S","South","Other") turns the code in B2 into a name, with Other as the fallback.

If no test in IFS is true, you get #N/A, so end with TRUE as a catch-all, as in the grade example. Put the tests in order, because IFS stops at the first one that is true. Both functions need Excel 2019 or Microsoft 365. The exercises below cover grades, shipping price bands, turning codes into names and commission rates.

  1. 1Grades with IFSGrade the score in B2 with IFS: 90 or more is "A", 70 or more is "B", and anything else is "C". Use TRUE as the last test.
  2. 2Shipping price bands with IFSPremiumShipping costs 45 up to 1 kg, 69 up to 5 kg and 110 for anything heavier. In C2, use IFS to find the price for the weight in B2.
  3. 3Turn codes into names with SWITCHPremiumIn C2, use SWITCH to turn the code in B2 into a name: "N" is "North", "S" is "South", and any other code is "Other".
  4. 4Commission rates with IFSPremiumReps earn 10% commission on sales of 10,000 or more, 5% from 5,000 and nothing below that. In C2, multiply the sales in B2 by the rate IFS picks: B2*IFS(B2>=10000,0.1,B2>=5000,0.05,TRUE,0).

Functions in this topic

Read the guide

Questions

Why does IFS return #N/A?

IFS returns #N/A when none of the tests is true, because it has no default answer. Add TRUE as the last test, followed by the value you want as the fallback, like =IFS(B2>=90,"A",B2>=70,"B",TRUE,"C"). Also check the order of your tests, since IFS stops at the first one that is true.

What is the difference between IFS and SWITCH?

IFS tests conditions such as >=70 or <=5, so it fits ranges like grades and price bands. SWITCH compares one value against a list of exact matches, so it fits codes like "N" and "S". If you need to check whether something is above or below a limit, use IFS. If you are translating exact values, SWITCH is shorter.

Is IFS available in Excel 2016?

Not in Excel 2016 or 2013 bought as a one-time license. IFS and SWITCH are in Excel 2019, Excel 2021 and Microsoft 365. In older versions, nest IF functions instead: =IF(B2>=90,"A",IF(B2>=70,"B","C")) gives the same result as the IFS grade formula.