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 ...
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.
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").