Help with IFS Search formula

[Evans2]

Hi all,

I have a column with text that I need to extract into another column. I'm using an IFS(Search) formula to do this which is working for some of the text in my first logical test but not the second even though the text is present.

formula
=IFS(SEARCH("GrainPro",M92),"GrainPro Bags",SEARCH("vacuum package",M92),"vacuum package")

result
[TABLE="width: 200"]
<tbody>[TR]
[TD]30 kg bag in GrainPro[/TD]
[TD]GrainPro Bags[/TD]
[/TR]
[TR]
[TD]30 kg vacuum package[/TD]
[TD]#Value![/TD]
[/TR]
</tbody>[/TABLE]

I'm not sure why it's not working for me. Any help is appreciated!

Thank you,

Evan

 

[Twollaston]

Hi all,

I have a column with text that I need to extract into another column. I'm using an IFS(Search) formula to do this which is working for some of the text in my first logical test but not the second even though the text is present.

formula
=IFS(SEARCH("GrainPro",M92),"GrainPro Bags",SEARCH("vacuum package",M92),"vacuum package")

result
[TABLE="width: 200"]
<tbody>[TR]
[TD]30 kg bag in GrainPro[/TD]
[TD]GrainPro Bags[/TD]
[/TR]
[TR]
[TD]30 kg vacuum package[/TD]
[TD]#VALUE ![/TD]
[/TR]
</tbody>[/TABLE]

I'm not sure why it's not working for me. Any help is appreciated!

Thank you,

Evan

It's because if the first condition is not true, it's returning an error, and doesn't run the else statement. Use If error to make the error return 0 instead. See below

=IF(IFERROR(SEARCH("GrainPro",M92)>0,0),"GrainPro Bags",IF(IFERROR(SEARCH("vacuum package",M92)>0,0),"vacuum package"))

For an if statement, you want the first condition to return true or false, or 1 or 0. Search("GrainPro", M92) will return #VALUE ! if grainpro is not in the cell, so for instances where that text is not present, we want it to return 0 instead of a value error, because when 0 is returned the if statement, knows it's time to run the value if false portion.

 

[Evans2]

It's because if the first condition is not true, it's returning an error, and doesn't run the else statement. Use If error to make the error return 0 instead. See below

=IF(IFERROR(SEARCH("GrainPro",M92)>0,0),"GrainPro Bags",IF(IFERROR(SEARCH("vacuum package",M92)>0,0),"vacuum package"))

For an if statement, you want the first condition to return true or false, or 1 or 0. Search("GrainPro", M92) will return #VALUE ! if grainpro is not in the cell, so for instances where that text is not present, we want it to return 0 instead of a value error, because when 0 is returned the if statement, knows it's time to run the value if false portion.

Thank you!!!