Nov 24 2021 06:48 AM
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)
Nov 24 2021 07:32 AM
Solution=IFS(COUNTIFS($H$3:$H$30,LEFT(B3,4)),$H$2,COUNTIFS($I$3:$I$18,LEFT(B3,4)),$I$2,TRUE,"no match")
Nov 24 2021 07:39 AM
=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?
Jan 03 2022 06:25 AM
@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
Jan 03 2022 06:29 AM
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)
Jan 03 2022 06:31 AM
Jan 03 2022 06:35 AM
Which exactly formula returns #N/A ?
Jan 03 2022 06:37 AM
Jan 03 2022 06:43 AM
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.
Jan 03 2022 06:47 AM
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")
Jan 03 2022 06:49 AM
It shall be here. If that option is not available for you, send me Private Message with file, I'll add to the thread.
Jan 03 2022 07:25 AM
Please check attached if it works.
Jan 03 2022 07:37 AM
Jan 03 2022 08:24 AM
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";...
Jan 03 2022 09:37 AM
@Sergei Baklan Thank you again for the quick help and explanation.
Nov 24 2021 07:32 AM
Solution=IFS(COUNTIFS($H$3:$H$30,LEFT(B3,4)),$H$2,COUNTIFS($I$3:$I$18,LEFT(B3,4)),$I$2,TRUE,"no match")