Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

IFS

Check multiple criteria and return the first match in Excel
Tests multiple conditions and returns corresponding result of first TURE condition

Rational

Check multiple criteria and return the first match.

Description

The Excel IFS function evaluate multiple criteria at once and return the first TRUE criteria match. In place of writing multiple IF statement, IFS can come handy to keep the formula more comfortable to comprehend.

Syntax

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

Parameters

logical_test – The criteria to evaluate.
value_if_ true [optional] – Corresponding value of criteria (logical_test).

Return Value

Return the corresponding value (value_if_ true) of TRUE criterion(logical_test).

Key Notes

  • The IFS function can test many arguments at once (logical_test) and return the corresponding value of first TRUE logic unlike IF function that can evaluate one logic at once.
  • The IFS function allows you to test up to 127 different conditions.
  • IFS returns the #N/A error if none of the logic tests is TRUE.

Example

For example, on the basis of Employee sales provide them, we  apply IFS formula =IFS(C6<=499,”0.0%”, C6 <= 850 , “2.0%”, C6<=1300,”4.0%”,C6>=1800,”5.0%”) it return commission according to given sales.

Sometimes IFS formula gives #NA as not match with test for this we will apply IFNA formula like here in cell F6 =IFNA(IFS(C4<500,0%,C6<100,2%,C6<1500,4%),5%) it return 5%.

 

 

Keep Reading Similar Functions:

Substitute #N/A error and replace with an alternate value in Excel

IFNA

The IFNA function evaluate given criterion (value) in case the return value is a #N/A error then it substitutes with a different text string (value_if_na).

Read More »
Return value based on given criteria in Excel

IF

The IF function is a handy logical operator that return value based on a valid test of criteria, if it matches (TRUE) and another value if it does not.

Read More »
Substitute the error result and replace with an alternate value in Excel

IFERROR

The IFERROR function evaluates a criterion in case of an error then it substitutes with a different text string. I.e.,=IFERROR(2/0,”Error”) return “Error”.

Read More »