 SOLVED

# 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)

17 Replies
best response confirmed by Ridi96 (New Contributor)
Solution

# Re: IFS formula gives #N/A error

``=IFS(COUNTIFS(\$H\$3:\$H\$30,LEFT(B3,4)),\$H\$2,COUNTIFS(\$I\$3:\$I\$18,LEFT(B3,4)),\$I\$2,TRUE,"no match")``

# Re: IFS formula gives #N/A error

=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?

# Re: IFS formula gives #N/A error

Very helpful and insightful, thank you!

# Re: IFS formula gives #N/A error

@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

# Re: IFS formula gives #N/A error

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)``

# Re: IFS formula gives #N/A error

Correct. My problem is that when i use IFS on a IFS formula, i get #N/A

# Re: IFS formula gives #N/A error

Which exactly formula returns #N/A ?

# Re: IFS formula gives #N/A error

The formula below gifs the #N/A
=IFS(E118=0;"5";E118=1;"4";E118=2;"3")

It tries to get the outcome of the formula below
=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")

If it's possible i can also upload the excel document with formula

# Re: IFS formula gives #N/A error

Yes, sample file will be great.

Mentioned formula could return #N/A if only in E118 you have text, not number. Perhaps in E118 you have another formula which returns texts instead of numbers.

# Re: IFS formula gives #N/A error

Sorry, another option if you have something what not in criteria options, e.g. E118 = 999. You may change on

``=IFS(E118=0;5; E118=1;4; E118=2;3; TRUE; "no such number")``

# Re: IFS formula gives #N/A error

I don't know how to ad a sample file

# Re: IFS formula gives #N/A error

It shall be here. If that option is not available for you, send me Private Message with file, I'll add to the thread. # Re: IFS formula gives #N/A error

Please check attached if it works.

# Re: IFS formula gives #N/A error

Looks like this is the fix. Could you also explain how, so i wont be making the same mistake again.

# Re: IFS formula gives #N/A error

The main point don't miss texts with numbers.

If you'd like to return number 5 use

``=IFS(E118=0;5;...``

not

``=IFS(E118=0;"5";...``

# Re: IFS formula gives #N/A error

@Sergei Baklan Thank you again for the quick help and explanation.

# Re: IFS formula gives #N/A error

@MischaTaba , you are welcome