May 24 2018
12:53 PM
- last edited on
Jul 31 2018
08:15 AM
by
TechCommunityAP
May 24 2018
12:53 PM
- last edited on
Jul 31 2018
08:15 AM
by
TechCommunityAP
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")
May 24 2018 08:32 PM
Hi Erin,
For the logical test instead of SEARCH(...) use ISNUMBER(SEARCH(...))
May 24 2018 08:35 PM
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.
Jun 26 2021 02:10 AM
Jun 26 2021 02:47 AM
@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").