Combining IFS and SEARCH functions

I want Excel to automatically produce certain categories when the cell next to it is filled in. So if the word "Certificaat" (I'm Dutch btw) is used in cell F136, then Excell has to assign the catego...

excel

Formulas and Functions

office 365

SergeiBaklan
Sep 12, 2018
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/

Forum Discussion

Combining IFS and SEARCH functions

Resources