Sep 18 2018 12:43 AM - edited Sep 18 2018 01:32 AM
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.
Sep 18 2018 01:04 AM
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.
Sep 18 2018 01:31 AM
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.
Sep 18 2018 02:41 AM - edited Sep 18 2018 02:42 AM
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..
Sep 18 2018 04:11 AM - edited Sep 18 2018 04:13 AM
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)
Sep 18 2018 04:30 AM
No its me, sorry..
=OR(IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619)),TRUE,FALSE))
the brackets were in the wrong place :\
Sep 18 2018 04:59 AM
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.
Sep 18 2018 05:54 AM
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.
Sep 18 2018 06:05 AM
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)
Sep 19 2018 05:39 AM
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.
Sep 19 2018 05:44 AM
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
Sep 19 2018 06:08 AM
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.
Sep 21 2018 04:09 AM
This works perfectly Sergei, thanks a lot. It took me a while to get it working but now it works.
Feb 21 2019 09:58 PM
@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,"")
Feb 21 2019 11:56 PM
@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.
Feb 22 2019 03:04 PM
Yes, IFS is not available for 2010 https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45
Feb 22 2019 03:14 PM
Yes, actually IFS is just another form of nested IF
Feb 28 2019 06:00 PM
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