Spreadsheet Practice

How to use IFS in Google Sheets

Google Sheets formula

=IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight")

List condition and result pairs, and end with TRUE as the catch-all: =IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight").

Result on the example
one result per row in C2, filled down to C9
Excel
See below

See it work

The formula is already in C2, filled down to C9. Change any input and the result updates, or edit the formula to experiment.

How it works

IFS checks the conditions in order and returns the result of the first one that is TRUE, so a 2.5 kg parcel stops at B2<5 and never reaches the Large test. The final TRUE,"Freight" pair always passes, which makes it the default for everything heavier.

=IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight")

Result"Small"

  1. B20.4

    The value in B2.

  2. B2<1TRUE

    The < operator tests less than.

  3. B2<5TRUE

    The < operator tests less than.

  4. B2<20TRUE

    The < operator tests less than.

  5. IFS(B2<1,"Small",B2<5,"Medium",B2<20,"Large",TRUE,"Freight")"Small"

    IFS: Returns the value for the first true test.

    • test1B2<1TRUE
    • value1"Small"
    • test2, value2B2<5TRUE
    • test2"Medium"
    • value2B2<20TRUE
    • test3"Large"
    • value3TRUE
    • test4"Freight"

In Excel

The same IFS formula works in Excel 2019 and later. In older versions, nest IF: =IF(B2<1,"Small",IF(B2<5,"Medium",IF(B2<20,"Large","Freight"))).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=IF(B2<1,"Small",IF(B2<5,"Medium",IF(B2<20,"Large","Freight")))Small, Medium, Large…Nested IF gives the same result and works in every version of both apps.

Common mistakes

  • Without the TRUE pair, a value that matches no condition returns #N/A.
  • Order matters: testing B2<20 first would label every parcel under 20 kg Large.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use IFS

In C2, label the parcel by weight: under 1 kg Small, under 5 Medium, under 20 Large, anything else Freight. Fill down to C9.

Questions

How do I use a lookup table instead?

Put the lower bounds in a table and use =VLOOKUP(B2,F2:G5,2,TRUE); changing a band then means editing a cell, not the formula.

What is the difference between IFS and SWITCH?

SWITCH compares one value with a list of exact matches; IFS takes any conditions, such as ranges.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.