Sep 12 2018 02:36 AM - edited Sep 12 2018 02:37 AM
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?
Sep 12 2018 03:13 AM
SolutionHi 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/
Feb 21 2021 06:27 AM
Feb 21 2021 07:30 AM
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.
Dec 09 2022 02:47 AM
Sep 12 2018 03:13 AM
SolutionHi 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/