Forum Discussion
Combining IFS and SEARCH functions
Hi Roy,
More correct will be
=IFS(ISNUMBER(SEARCH("Certificaat",F136)),"Diploma's & certificaten",ISNUMBER(SEARCH("Vaste aanstelling",F136)),"Vaste aanstelling",TRUE,"")if you use English version of Excel. In your variant if first search doesn't find the entry it returns #VALUE and entire formula also returns #VALUE. TRUE condition at the end if no one is found.
For Dutch version the formula is
=ALS.VOORWAARDEN(ISGETAL(VIND.SPEC("Certificaat";F136));"Diploma's & certificaten";ISGETAL(VIND.SPEC("Vaste aanstelling";F136));"Vaste aanstelling";WAAR;"")I translated it here https://en.excel-translator.de/translator/
Hi Roy,
More correct will be
=IFS(ISNUMBER(SEARCH("Certificaat",F136)),"Diploma's & certificaten",ISNUMBER(SEARCH("Vaste aanstelling",F136)),"Vaste aanstelling",TRUE,"")
if you use English version of Excel. In your variant if first search doesn't find the entry it returns #VALUE and entire formula also returns #VALUE. TRUE condition at the end if no one is found.
For Dutch version the formula is
=ALS.VOORWAARDEN(ISGETAL(VIND.SPEC("Certificaat";F136));"Diploma's & certificaten";ISGETAL(VIND.SPEC("Vaste aanstelling";F136));"Vaste aanstelling";WAAR;"")
I translated it here https://en.excel-translator.de/translator/
- =IFS(ISNUMBER(SEARCH("Certificaat",F136)),"Diploma's
Why did you use isnumber? If F1 doesn't contain a number?SEARCH("Certificaat",F136) returns a number if "Certificaat" is found in F136, but an error value if it is not found.
So ISNUMBER(SEARCH("Certificaat",F136)) returns TRUE if "Certificaat" is found, FALSE if not.
- You are a legend, twice in 1 day your posts have helped me
Thank you very much.
That helped a lot!Roy, you are welcome
