Forum Discussion
IFS formula gives #N/A error
Hello,
I have a simple IFS formula in my sheet. Its job is to look for a certain value in a certain range, if found it should return a specified value. And it does it's job at the first logical test, but gives me an #N/A error when trying the second one. Could anyone help me with this?
I will attach the excel file for clarity.
Here is the formula:
=IFS(MATCH(LEFT(B3,4),$H$3:$H$30,0),$H$2,MATCH(LEFT(B3,4),$I$3:$I$18,0),$I$2)
=IFS(COUNTIFS($H$3:$H$30,LEFT(B3,4)),$H$2,COUNTIFS($I$3:$I$18,LEFT(B3,4)),$I$2,TRUE,"no match")
17 Replies
=IF(NOT(ISNA(MATCH(LEFT(B3,4),$H$3:$H$30,0))),$H$2,IF(NOT(ISNA(MATCH(LEFT(B3,4),$I$3:$I$18,0))),$I$2))
Is this what you are looking for?
- Very helpful and insightful, thank you!
=IFS(COUNTIFS($H$3:$H$30,LEFT(B3,4)),$H$2,COUNTIFS($I$3:$I$18,LEFT(B3,4)),$I$2,TRUE,"no match")Detlef_Lewin I also have a problem with the IFS, could you help me out?
I am trying to have a outcome on IFS formula with a IFS formula.
In stock Stock level must be To order Outcome must be a number Banana 1 3 =IFS(F118=0;"0";F118=0,5;"1";F118=1;"1";F118=1,5;"1";F118=2;"2";F118=2,5;"3";F118=3;"0") =SUM(D118-C118) =IFS(E118=0;"5";E118=1;"4";E118=2;"3") Banana =IF(E118=0;"5";IF(E118=1;"4";IF(E118=2;"3"))) Banana Thnx in advance
I guess you'd like to return numbers, not texts
=IFS(F118=0;0;F118=0,5;1;F118=1;1;F118=1,5;1;F118=2;2;F118=2,5;3;F118=3;0)
