• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IFERROR SEARCH WITH MULTIPLE CONDITION

S

Sg2209

New Member
Dear Friends ,

Could anyone please help with the below Formula

=IFERROR(IF(SEARCH("*CITATION*",Q2,1),"CITE"),"Core"+=IFERROR(IF(SEARCH("*A PPROVAL*",Q2,1),"APPROVED"),"Core"+=IFERROR(IF(SEARCH("*INVITATION*",E2,1), "INVITE"),"Core"

i wanted to convert this in multiple condition and in different columns too , is it possible
 
Can you upload a sample workbook please.
Now your formula cannot work with those intermediate plus and equal signs. Not in that way anyway and there might be some other syntax errors.

You can use AND/OR to test multiple conditions... Do all the conditions need to be true, then use and.
iferror might be not necessary and could be replaced with the false part of the if functions (depending on your data), or wrap the iferror round the nested if, where the new if is inside the false part of the previous if.
Each of the tests you do can refer to other columns.

would this do as build up?
= and (if (SEARCH(find_text,within_text;start_num), true, false), if (SEARCH(find_text;within_text;start_num), true, false),if (SEARCH(find_text;within_text;start_num), true, false))
 
Hui said:
SG2209

Firstly, Welcome to the Chandoo.org Forums

Please note that your post has been cross-posted to other Excel sites.
Although not forbidden, it is courtesy to inform people as such.
Also please post a solution if the question is solved elsewhere or here

Please take a few minutes to read through the site rules
https://chandoo.org/forum/threads/new-users-please-read.294/
Click to expand...
GraH - Guido said:
Can you upload a sample workbook please.
Now your formula cannot work with those intermediate plus and equal signs. Not in that way anyway and there might be some other syntax errors.

You can use AND/OR to test multiple conditions... Do all the conditions need to be true, then use and.
iferror might be not necessary and could be replaced with the false part of the if functions (depending on your data), or wrap the iferror round the nested if, where the new if is inside the false part of the previous if.
Each of the tests you do can refer to other columns.

would this do as build up?
= and (if (SEARCH(find_text,within_text;start_num), true, false), if (SEARCH(find_text;within_text;start_num), true, false),if (SEARCH(find_text;within_text;start_num), true, false))
Click to expand...


thank you so much for the quick response however it did not work , getting an error # VALUE
 
Hi ,
i was suggest by my fried to use the below formula and need to use copy & paste on rest of the cells. also find the similar examples on google

=IF(ISNUMBER(SEARCH("CITATION",Q2,1))=TRUE,"CITE",IF(ISNUMBER(SEARCH("APPROVAL",Q2,1))=TRUE,"Approved",IF(ISNUMBER(SEARCH("INVITATION",Q2,1))=TRUE,"Invite","Core")))
 
Sg2209 said:
.......the similar examples on google
=IF(ISNUMBER(SEARCH("CITATION",Q2,1))=TRUE,"CITE",IF(ISNUMBER(SEARCH("APPROVAL",Q2,1))=TRUE,"Approved",IF(ISNUMBER(SEARCH("INVITATION",Q2,1))=TRUE,"Invite","Core")))
Click to expand...

Try to simplify like this :

=IF(ISNUMBER(SEARCH("CITATION",Q2)),"CITE",IF(ISNUMBER(SEARCH("APPROVAL",Q2)),"Approved",IF(ISNUMBER(SEARCH("INVITATION",Q2)),"Invite","Core")))

or,

=IFERROR(INDEX({"CITE","Approved","Invite"},MATCH(1,0+ISNUMBER(SEARCH(Q2,{"CITATION","APPROVAL","INVITATION"})),0)),"Core")

or,

=IFERROR(LOOKUP(2,1/SEARCH(Q2,{"CITATION","APPROVAL","INVITATION"}),{"CITE","Approved","Invite"}),"Core")

Regards
Bosco
 
bosco_yip said:
Try to simplify like this :

=IF(ISNUMBER(SEARCH("CITATION",Q2)),"CITE",IF(ISNUMBER(SEARCH("APPROVAL",Q2)),"Approved",IF(ISNUMBER(SEARCH("INVITATION",Q2)),"Invite","Core")))

or,

=IFERROR(INDEX({"CITE","Approved","Invite"},MATCH(1,0+ISNUMBER(SEARCH(Q2,{"CITATION","APPROVAL","INVITATION"})),0)),"Core")

or,

=IFERROR(LOOKUP(2,1/SEARCH(Q2,{"CITATION","APPROVAL","INVITATION"}),{"CITE","Approved","Invite"}),"Core")

Regards
Bosco
Click to expand...
Impressive Bosco, once more. I like the given alternatives 2+3. I hardly ever consider using literal arrays like this. I was thinking about ISNUMBER as well, but I was too lazy to log on again. It might inspire me for a blog-post at work. Thank you sir!
Sg2209 said:
thank you so much for the quick response however it did not work , getting an error # VALUE
Click to expand...
That's why I asked for a sample file... But please do try one of the above given by Bosco.
 
GraH - Guido said:
Impressive Bosco, once more. I like the given alternatives 2+3. I hardly ever consider using literal arrays like this. I was thinking about ISNUMBER as well, but I was too lazy to log on again. It might inspire me for a blog-post at work. Thank you sir!

That's why I asked for a sample file... But please do try one of the above given by Bosco.
Click to expand...
Worked , Great Appreciate your efforts
 
You must log in or register to reply here.
Back
Top