IFS()

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 subtraction, ANY(), 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(condition1, value1, [condition2, value2 ...])

• condition1 - A Yes/No expression that returns TRUE or FALSE.
• value1 - An expression to be evaluated only if condition1 evaluates as TRUE.

• condition2, value2 - (Optional) Additional logical expressions and values if the first set doesn't evaluate to TRUE.

Every value 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()