IFS formula gives #N/A error

[Ridi96]

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)

[#2 · Accepted 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")

[#3]

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

[#4]

Very helpful and insightful, thank you!

[#5]

@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

[#6]

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

[#7]

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

[#8]

@MischaTaba 

Which exactly formula returns #N/A ?

[#9]

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

[#10]

@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.

[#11]

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

[#12]

I don't know how to ad a sample file

[#13]

@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.

[#14]

@MischaTaba 

Please check attached if it works.

[#15]

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

[#16]

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

[#17]

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

[#18]

@MischaTaba , you are welcome