Forum Discussion

Roy Verdonschot's avatar
Roy Verdonschot
Copper Contributor
Sep 12, 2018
Solved

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's avatar
    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/

     

     

jtjohnston_quebec's avatar
jtjohnston_quebec
Copper Contributor
Feb 21, 2021
=IFS(ISNUMBER(SEARCH("Certificaat",F136)),"Diploma's

Why did you use isnumber? If F1 doesn't contain a number?
HansVogelaar's avatar
HansVogelaar
MVP
Feb 21, 2021

jtjohnston_quebec 

 

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.

  • SFen90's avatar
    SFen90
    Copper Contributor
    Dec 09, 2022
    You are a legend, twice in 1 day your posts have helped me

Resources