Forum Discussion
IFS statement with SEARCH not working
The IFS statement below works with the exception of the SEARCH part I've added at the end. If cell C1105 contains 1%, it will work, but if it contains 2% or 4%, it will not work. For some reason any search parameters only works for the first instance.
=IFS(H1105 = "4% MILK FAT","4% LOW FAT",H1105="LOW FAT","LOW FAT",H1105="1% MILK FAT","1% LOW FAT",H1105="1% LOW FAT","1% LOW FAT",H1105="FAT FREE","FAT FREE",H1105="2% LOW FAT","2% LOW FAT",H1105="2% MILK FAT","2% LOW FAT",H1105="NONFAT", "FAT FREE",H1105="REGULAR FAT","4% LOW FAT",H1105="NO FAT", "FAT FREE",H1105=".5% MILK FAT",".5% LOW FAT",H1105="4% LOW FAT","4% LOW FAT",H1105="1.5% MILK FAT","1.5% LOW FAT",H1105="NOT STATED ON PACKAGE","UNKNOWN",H1105="VALUE UNKNOWN","UNKNOWN",SEARCH("1%",C1105),"1% LOW FAT",SEARCH("2%",C1105),"2% LOW FAT",SEARCH("4%",C1105),"4% LOW FAT")
4 Replies
Erin-
It's difficult to tell what you're trying to do with the formula alone. Is there anyway you could please provide a non-sensitive example file to give a little context to your data structure and the particular scenario you currently have. A before and after would be nice as well to help understand what values your current formula is currently yielding and what you would expect it to yield if it was working correctly.
Hi Erin,
For the logical test instead of SEARCH(...) use ISNUMBER(SEARCH(...))
- Fixed the issue =).
I was using: IFS(search("x", cell x) > 0, y, repeat n times...)
but this only worked for the first search request, and all subsequent searches were not checked. With ISNUMBER(Search(...)) all the searches fired. Good stuff. I presume this is because error > 0 is an error which exits the IFS function immediately? Magic.
Thank you.luke_B1835 , glad it helped.
IFS() checks conditions one by one and returns results for the first condition which is TRUE or has an error, returning error in the latest case.
SEARCH returns some number if text was found (equivalent of logical TRUE) or an error otherwise. Latest stops IFS().
ISNUMBER(SEARCH()) returns TRUE if text was found and FALSE if not. If the latest IFS() skips this condition and goes to check next one.
In addition, it's always better to have TRUE condition as the last in IFS() if no one condition met. Like
=IFS(..., TRUE, "no match").
