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


Check multiple criteria and return the first match.


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.


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


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.


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:

Returns a different result if the first argument evaluates to an error


How to change the Excel formula result like #DIV, #VALUE!, #REF!, #NAME?, #NUM! by another value like string, blank, and zero with the IFERROR function.

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


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


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 »