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 category "Diploma's & certificaten. But if the word "Vaste aanstelling" is used somewhere in cell F136 the category has to be "Vaste aanstelling".
These two words are never used in the same cell so this should work.

 

So I created this function:

=IFS(SEARCH("Certificaat";F136)>0;"Diploma's & certificaten";SEARCH("Vaste aanstelling";F136)>0;"Vaste aanstelling")

But this doesn't work. It gives the error: #VALUE
What did I do wrong? I thought I could use more than one condition with IFS?


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/

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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.

Resources