• 466K Members
• 10.5K Online
• 563K Conversations

## IFS(OR(ISNUMER(SEARCH( function

Occasional Contributor

# IFS(OR(ISNUMER(SEARCH( function

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

# Re: IFS(OR(ISNUMER(SEARCH( funtion

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.

# Re: IFS(OR(ISNUMER(SEARCH( function

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.

# Re: IFS(OR(ISNUMER(SEARCH( function

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

# Re: IFS(OR(ISNUMER(SEARCH( function

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)

# Re: IFS(OR(ISNUMER(SEARCH( function

No its me, sorry..

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

the brackets were in the wrong place :-/

# Re: IFS(OR(ISNUMER(SEARCH( function

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.

# Re: IFS(OR(ISNUMER(SEARCH( function

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.

# Re: IFS(OR(ISNUMER(SEARCH( function

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)

# Re: IFS(OR(ISNUMER(SEARCH( function

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.

# Re: IFS(OR(ISNUMER(SEARCH( function

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

# Re: IFS(OR(ISNUMER(SEARCH( function

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

# Re: IFS(OR(ISNUMER(SEARCH( function

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

# Re: IFS(OR(ISNUMER(SEARCH( function

Roy, you are welcome

# Re: IFS(OR(ISNUMER(SEARCH( function

@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,"")

# Re: IFS(OR(ISNUMER(SEARCH( function

@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-")))))))))))

Hope this works.

# Re: IFS(OR(ISNUMER(SEARCH( function

Yes, actually IFS is just another form of nested IF

# Re: IFS(OR(ISNUMER(SEARCH( function

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

# Re: IFS(OR(ISNUMER(SEARCH( function

"IFS" is not recognized in 2016. Should I use 2019 instead?

# Re: IFS(OR(ISNUMER(SEARCH( function

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies