SOLVED

New Contributor

# 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?

5 Replies
best response confirmed by Roy Verdonschot (New Contributor)
Solution

# Re: 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/

# Re: Combining IFS and SEARCH functions

Thank you very much.
That helped a lot!

# Re: Combining IFS and SEARCH functions

Roy, you are welcome

# Re: Combining IFS and SEARCH functions

=IFS(ISNUMBER(SEARCH("Certificaat",F136)),"Diploma's

Why did you use isnumber? If F1 doesn't contain a number?

# Re: Combining IFS and SEARCH functions

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.