IFS statement not working with SEARCH function

[erinmagee]

It seems that the last part of this statement that uses the SEARCH formula is not working (highlighted in red). If cell C2 contains "1%", it will return the correct answer, but if it contains "2% or "4%", then it will return #value .

Basically, the formula recognizes the first SEARCH, but not the last 2.

=IFS(H2 = "4% MILK FAT","4% LOW FAT",H2="LOW FAT","LOW FAT",H2="1% MILK FAT","1% LOW FAT",H2="1% LOW FAT","1% LOW FAT",H2="FAT FREE","FAT FREE",H2="2% LOW FAT","2% LOW FAT",H2="2% MILK FAT","2% LOW FAT",H2="NONFAT", "FAT FREE",H2="REGULAR FAT","4% LOW FAT",H2="NO FAT", "FAT FREE",H2=".5% MILK FAT",".5% LOW FAT",H2="4% LOW FAT","4% LOW FAT",H2="1.5% MILK FAT","1.5% LOW FAT",H2="NOT STATED ON PACKAGE","UNKNOWN",H2="VALUE UNKNOWN","UNKNOWN",SEARCH("1%",C2),"1% LOW FAT",SEARCH("2%",C2),"2% LOW FAT",SEARCH("4%",C2),"4% LOW FAT")

I have no idea why this is not working - I tried putting the SEARCH sequence at the beginning and still got the same results. The statement without the SEARCH part works just fine. HELP!

 

[jtakw]

Hi,

First of all, I Don't have the IFS function, but I believe what you're missing is the ISNUMBER argument for the SEARCH function you're using, if the SEARCH can Not find the value, it will produce a #VALUE error, so try this:

Book1
	E
1	#NAME?
Sheet63

Cell Formulas
Range	Formula
E1	=IFS(H2 = "4% MILK FAT","4% LOW FAT",H2="LOW FAT","LOW FAT",H2="1% MILK FAT","1% LOW FAT",H2="1% LOW FAT","1% LOW FAT",H2="FAT FREE","FAT FREE",H2="2% LOW FAT","2% LOW FAT",H2="2% MILK FAT","2% LOW FAT",H2="NONFAT", "FAT FREE",H2="REGULAR FAT","4% LOW FAT",H2="NO FAT", "FAT FREE",H2=".5% MILK FAT",".5% LOW FAT",H2="4% LOW FAT","4% LOW FAT",H2="1.5% MILK FAT","1.5% LOW FAT",H2="NOT STATED ON PACKAGE","UNKNOWN",H2="VALUE UNKNOWN","UNKNOWN",ISNUMBER(SEARCH("1%",C2)),"1% LOW FAT",ISNUMBER(SEARCH("2%",C2)),"2% LOW FAT",ISNUMBER(SEARCH("4%",C2)),"4% LOW FAT")

Also, I see some redundancies in your formula, you can probably shorten it by adding OR (may be even SEARCH) functions for same "Value if True" tests.