IFS formula gives #N/A error

New Contributor


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:

17 Replies
best response confirmed by Ridi96 (New Contributor)


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




Is this what you are looking for?

Very helpful and insightful, thank you!

@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 stockStock level must beTo order  Outcome must be a number   
Banana13=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  


Thnx in advance


I guess you'd like to return numbers, not texts

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


Which exactly formula returns #N/A ?

The formula below gifs the #N/A

It tries to get the outcome of the formula below

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


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.


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


I don't know how to ad a sample file


It shall be here. If that option is not available for you, send me Private Message with file, I'll add to the thread.



Please check attached if it works.

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


The main point don't miss texts with numbers.

If you'd like to return number 5 use




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

@MischaTaba , you are welcome