SOLVED

Combining IFS and SEARCH functions

%3CLINGO-SUB%20id%3D%22lingo-sub-251593%22%20slang%3D%22en-US%22%3ECombining%20IFS%20and%20SEARCH%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251593%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20Excel%20to%20automatically%26nbsp%3Bproduce%20certain%20categories%20when%20the%20cell%20next%20to%20it%20is%20filled%20in.%20So%20if%20the%20word%20%22Certificaat%22%20(I'm%20Dutch%20btw)%26nbsp%3Bis%20used%20in%20cell%20F136%2C%20then%20Excell%20has%20to%20assign%20the%20category%20%22Diploma's%26nbsp%3B%26amp%3B%20certificaten.%20But%20if%20the%20word%20%22Vaste%20aanstelling%22%20is%20used%20somewhere%20in%20cell%20F136%20the%20category%20has%20to%20be%20%22Vaste%20aanstelling%22.%3CBR%20%2F%3EThese%20two%20words%20are%20never%20used%20in%20the%20same%20cell%20so%20this%20should%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20created%20this%20function%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIFS(SEARCH(%22Certificaat%22%3BF136)%26gt%3B0%3B%22Diploma's%20%26amp%3B%20certificaten%22%3BSEARCH(%22Vaste%20aanstelling%22%3BF136)%26gt%3B0%3B%22Vaste%20aanstelling%22)%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20this%20doesn't%20work.%20It%20gives%20the%20error%3A%20%23VALUE%3CBR%20%2F%3EWhat%20did%20I%20do%20wrong%3F%20I%20thought%20I%20could%20use%20more%20than%20one%20condition%20with%20IFS%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-251593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251621%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS%20and%20SEARCH%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251621%22%20slang%3D%22en-US%22%3E%3CP%3ERoy%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251620%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS%20and%20SEARCH%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251620%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much.%3CBR%20%2F%3EThat%20helped%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-251604%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS%20and%20SEARCH%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-251604%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Roy%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20correct%20will%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFS(ISNUMBER(SEARCH(%22Certificaat%22%2CF136))%2C%22Diploma's%20%26amp%3B%20certificaten%22%2CISNUMBER(SEARCH(%22Vaste%20aanstelling%22%2CF136))%2C%22Vaste%20aanstelling%22%2CTRUE%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eif%20you%20use%20English%20version%20of%20Excel.%20In%20your%20variant%20if%20first%20search%20doesn't%20find%20the%20entry%20it%20returns%20%23VALUE%20and%20entire%20formula%20also%20returns%20%23VALUE.%20TRUE%20condition%20at%20the%20end%20if%20no%20one%20is%20found.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Dutch%20version%20the%20formula%20is%3C%2FP%3E%0A%3CPRE%3E%3DALS.VOORWAARDEN(ISGETAL(VIND.SPEC(%22Certificaat%22%3BF136))%3B%22Diploma's%20%26amp%3B%20certificaten%22%3BISGETAL(VIND.SPEC(%22Vaste%20aanstelling%22%3BF136))%3B%22Vaste%20aanstelling%22%3BWAAR%3B%22%22)%3C%2FPRE%3E%0A%3CP%3EI%20translated%20it%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fen.excel-translator.de%2Ftranslator%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fen.excel-translator.de%2Ftranslator%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153426%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS%20and%20SEARCH%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153426%22%20slang%3D%22en-US%22%3E%3DIFS(ISNUMBER(SEARCH(%22Certificaat%22%2CF136))%2C%22Diploma's%3CBR%20%2F%3E%3CBR%20%2F%3EWhy%20did%20you%20use%20isnumber%3F%20If%20F1%20doesn't%20contain%20a%20number%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153500%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS%20and%20SEARCH%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973750%22%20target%3D%22_blank%22%3E%40jtjohnston_quebec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESEARCH(%22Certificaat%22%2CF136)%20returns%20a%20number%20if%20%22Certificaat%22%20is%20found%20in%20F136%2C%20but%20an%20error%20value%20if%20it%20is%20not%20found.%3C%2FP%3E%0A%3CP%3ESo%20ISNUMBER(SEARCH(%22Certificaat%22%2CF136))%20returns%20TRUE%20if%20%22Certificaat%22%20is%20found%2C%20FALSE%20if%20not.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

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/

 

 

Thank you very much.
That helped a lot!

Roy, you are welcome

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

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

@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.