IFS()

First-match conditional evaluation

Returns the results of a first-match conditional evaluation. If the initial argument evaluates as TRUE, returns the result of evaluating the second argument.

Sample usage

IFS(TRUE, "Yes!") returns Yes!

IFS(FALSE, "Yes!") returns blank (no value)

IFS(FALSE, "Yes!", TRUE, "No!") returns No! (equivalent to IF(FALSE, "Yes!", "No!"). See also: IF()

Preferred phone number

Choose a preferred phone number from those available.

IFS(
  ISNOTBLANK([Mobile Phone]), [Mobile Phone],
  ISNOTBLANK([Work Phone]), [Work Phone],
  ISNOTBLANK([Home Phone]), [Home Phone]
)

Equivalent to:

ANY(
  LIST(
    [Mobile Phone]
    [Work Phone],
    [Home Phone]
  )
  - LIST("")
)

See also: List subtractionANY(), ISNOTBLANK()LIST()

Summarize delivery

Produce a textual summary of the time before a due date, e.g., of a delivery or project completion.

IFS(
  ISBLANK([Due Date]),
    "Unscheduled",
  (TODAY() > [Due Date]),
    "Overdue!",
  (TODAY() = [Due Date]),
    "Due today",
  (TODAY() = ([Due Date] - 1)),
    "Due tomorrow",
  TRUE,
    (([Due Date] - TODAY()) & " days remain")
)
  1. ISBLANK([Due Date]), "Unscheduled" returns Unscheduled if the Due Date column value is blank. No further conditions (below) are considered. See also: ISBLANK()

  2. If no preceding conditions (above) matched, (TODAY() > [Due Date]), "Overdue!" returns Overdue! if today's date is after the due date. No further conditions are considered. See also: TODAY()
  3. If no preceding conditions matched, (TODAY() = [Due Date]), "Due today" returns Due today if today's date is the due date. No further conditions are considered.
  4. If no preceding conditions matched, (TODAY() = ([Due Date] - 1)), "Due tomorrow" returns Due tomorrow if today is the day before the due date. No further conditions are considered.
  5. If no preceding conditions matched, TRUE, (([Due Date] - TODAY()) & " days remain") returns a message indicating the number of days until due.

Weekday name

Convert a weekday number to a name.

IFS(
  (WEEKDAY(TODAY()) = 1), "Sunday",
  (WEEKDAY(TODAY()) = 2), "Monday",
  (WEEKDAY(TODAY()) = 3), "Tuesday",
  (WEEKDAY(TODAY()) = 4), "Wednesday",
  (WEEKDAY(TODAY()) = 5), "Thursday",
  (WEEKDAY(TODAY()) = 6), "Friday",
  TRUE, "Saturday",
)

Functionally equivalent to but less efficient than:

SWITCH(
  WEEKDAY(TODAY()),
  1, "Sunday",
  2, "Monday",
  3, "Tuesday",
  4, "Wednesday",
  5, "Thursday",
  6, "Friday",
  "Saturday",
)

See also: SWITCH(), TODAY(), WEEKDAY()

Syntax

IFS(is-true?, then-do-this, [is-true?, then-do-this]... )

  • is-true? - A Yes/No expression that returns TRUE or FALSE.
  • then-do-this  - An expression to be evaluated only if the immediately preceding is-true? expression evaluates as TRUE.

Every then-do-this expression should produce results of comparable types (such as, all textual or all numeric). The results may all be single values or lists.

See also

IF()

SWITCH()

Was this helpful?
How can we improve it?

Need more help?

Try these next steps:

Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false
false
false
false