Home

IFS(OR(ISNUMER(SEARCH( function

roy verdonschot
Occasional Contributor

Is there a way to simplify this part of my IFS function:

 

IFS(OR(ISNUMBER(SEARCH("aanvraag opleiding",F619)),ISNUMBER(SEARCH("Aanvraag cursus",F619)),ISNUMBER(SEARCH("Toekenning aanvraag",F619)),ISNUMBER(SEARCH("Inschrijving opleiding",F619)),ISNUMBER(SEARCH("Coachingstraject",F619)),ISNUMBER(SEARCH("Volgen cursus",F619))),"Opleiding/training/coaching";...........................


It goes on after this and I have more of these repeated ISNUMBER(SEARCH( functions as logical tests and want to shorten them.

21 Replies

Are you testing to see if any of those words show up in F619?

 

How about creating a list of words to check and then using match as your logic..

 

Something like, =if(isnumber(match(F619,Sheet2!$A$2:$A$10,0)),True,False)

 

even better would be to make it a table or named range. This would also have the advantage of making it really easy to add/change/remove things to search for at a later date.

 

Yes, that's what I'm testing. I like the idea, but I have to find the words in a bigger line of text within F619.

Ah. Try this then..

=or(if(isnumber(search(Sheet2!A2:A10)),F619,True,False))

 

You need to enter it as an array ctrl+shift+enter..

I tried your formula a few times and tried to adjust some things but I can't get it to work. It says that I have provided too few arguments for this function.

Search needs a location where to search between the parentheses so on the dots: =or(if(isnumber(search(Sheet2!A2:A10;.......)),F619,True,False))
And where you put "False" shouldn't be anything because 'if(' needs only three values: logical test, value if true and value if false and you put four.
(I'm Dutch so some of the translations of "value if true" etc. can be a little bit off, but the functions are translated correctly)

No its me, sorry..

=OR(IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619)),TRUE,FALSE))

 

the brackets were in the wrong place :-/

Hm, this still doesn't solve my original problem. I need the function to produce a certain category. And that category depends on which word the function finds. So if F619 contains a word from Sheet2!$A$2:$A$10 it should produce Sheet2!$B$2 and when F619 contains a word from Sheet2!$A$11:$A$20 it should produce Sheet2!$B$11.

I thought of:
IFS(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619)),Sheet2!$B$2,ISNUMBER(SEARCH(Sheet2!$A$11:$A$20,F619)),Sheet2!$B$11)


But that one doesnt work for some reason. It gives #N/A except for Sheet2!$A$2, then it produces Sheet2!$B$2 like it should.

Mmmm does the attached do what you need?

 

It should, check the text for any of the words in the list, return the 'category' from the column next to it.

 

Perhaps

=IFS(SUM(--ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619))),Sheet2!$B$2,SUM(--ISNUMBER(SEARCH(Sheet2!$A$11:$A$20,F619))),Sheet2!$B$11,TRUE,"")

(array formula)

 

This formula constantly gives Sheet2!$B$2 as a result even if I fill in something between Sheet2!$A$11:$A$20 in F619. Why did u use SUM(-- in this formula though? I don't understand.

This doesn't work with how my data is organised. It is like this:

Big category          medium category     small category

                                                               small category

                              medium category

Big category          medium category     small category

                                                              small category

                              medium category

Big categorty         medium category

 

                              medium category

Hi Roy,

 

Please check in attached, I only changed F619 on A1.

ISNUMBER returns TRUE if something was found, otherwise FALSE.

-- converts TRUE to 1 and FALSE to 0.

SUM returns sum of above array, if at least one word maths it'll be positive number, otherwise zero.

IFS considers positive number as TRUE, zero as FALSE.

IFS works till first TRUE condition. If F619 found both in first and second ranges it returns result for the first range.

okey

This works perfectly Sergei, thanks a lot. It took me a while to get it working but now it works.

Roy, you are welcome


@Sergei Baklan wrote:

Roy, you are welcome


HELLO Sir, I happen to have the same problem.. My mentor found your thread and I applied your solution Using MICROSOFT OFFICE 2010.. Unfortunately, the solution didn't worked for me..
Here is my Formula

 


=IFS(SUM(--ISNUMBER(SEARCH(Sheet2!$B$2:$B$21,A2))),Sheet2!$C$2,SUM(--ISNUMBER(SEARCH(Sheet2!$B$22:$B$25,A2))),Sheet2!$C$23,TRUE,"")


@Sergei Baklan wrote:

Roy, you are welcome



Hello Sir, I just want to upload other solution for this thread.

My mentor used

 

=IF(COUNT(SEARCH({"Aliaga","Cabanatuan","Lourdes","Lupao","Munoz","San Jose","Talavera","Cabiao","Gabaldon ","Gapan ","General tinio","Jaen","Palayan","Cuyapo","Guimba","Quezon","Sta Rosa","Zaragosa","Rizal","BONGABON"},B74)), "NUEVA ECIJA", IF(COUNT(SEARCH({"Baler","Casiguran","Dipaculao","Maria"},B74)), "AURORA",IF(COUNT(SEARCH({"Camiling","Concepcion","Paniqui","Sta Ignacia","Tarlac","Victoria"},B74)), "TARLAC",IF(COUNT(SEARCH({"Baliuag","Plaridel","San Miguel","San Rafael","Sta Maria"},B74)),"BULACAN",IF(COUNT(SEARCH({"Angeles","Magalang","Mexico"},B74)),"PAMPANGA",IF(COUNT(SEARCH({"Agoo","San Fernando","Binmaley","Calasiao","Mangaldan","Rosales","Rosario","Tayug","Urdaneta"},B74)),"LA UNION",IF(COUNT(SEARCH({"Aritao","Bagabag","Bambang","Dupax","Solano"},B74)),"NUEVA VIZCAYA", IF(COUNT(SEARCH({"LAGAWE"},B74)),"IFUGAO",IF(COUNT(SEARCH({"Benito Soliven","Cabagan","Cabatuan","Cauayan","Ilagan","Mallig","Roxas","Tumauini","Echague","San Mateo","Alicia","Santiago","Jones"},B74)),"ISABELA",IF(COUNT(SEARCH({"Cabarroguis","Madella"},B74)),"QUIRINO",IF(COUNT(SEARCH({"Alcala","Baggao","Piat","Tuguegarao","Tuguegarao Carig","Tuguegarao South","Abulug","Allacapan","Aparri","Gattaran","Gonzaga"},B74)), "CAGAYAN", "-OTHERS-")))))))))))

 

instead.

 

Hope this works.

Yes, actually IFS is just another form of nested IF

Is it ok to use Ms Office 2016?


@Sergei Baklan wrote:

Yes, IFS is not available for 2010 https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45


 

"IFS" is not recognized in 2016. Should I use 2019 instead?
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
205 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies