SOLVED

IFS formula gives #N/A error

New Contributor

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

@Ridi96 

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

@Ridi96 

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

@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  
      =IF(E118=0;"5";IF(E118=1;"4";IF(E118=2;"3")))Banana  

 

Thnx in advance

@MischaTaba 

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)
Correct. My problem is that when i use IFS on a IFS formula, i get #N/A

@MischaTaba 

Which exactly formula returns #N/A ?

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

@MischaTaba 

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.

@MischaTaba 

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

@MischaTaba 

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

image.png

@MischaTaba 

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.

@MischaTaba 

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";...

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

@MischaTaba , you are welcome