Issue with nested IF formula

[sudhansu121]

Hi Experts,
Can I have a helping hand here with my problem.

I am currently putting a nested if with multiple condition and wild card but its not working.

Comments	Status
SSM:NAR	Resolved
AD:NAR	Resolved
SSM:To be queried	Open
AD:Open query	Open
AD:Added to permanet issue	Permanent issue

What I want is,
IF Comments column contains these text then the status column should be updated as shown in Status.

This is what formula I am trying to put, may be some more efficient formula is available but I am not sure.

=IF(OR(ISNUMBER(SEARCH("*NAR*",I:I)), "Resolved", ""), IF(OR(ISNUMBER(SEARCH("*To be queried*",I:I)), "Open", ""))

Thanks

 

[BenMiller]

Try this in J2 and drag down:
=IF(ISNUMBER(SEARCH("NAR",I2)),"Resolved",IF(ISNUMBER(SEARCH("To Be Queried",I2)),"Open",""))

 

[sudhansu121]

Try this in J2 and drag down:
=IF(ISNUMBER(SEARCH("NAR",I2)),"Resolved",IF(ISNUMBER(SEARCH("To Be Queried",I2)),"Open",""))

Thanks Dear, this is working fine. Have a great day ahead

 

[smozgur]

Thanks Dear, this is working fine. Have a great day ahead

@sudhansu121 - I marked post#2 as the solution in this question as you also confirmed.
Please mark the solution post next time instead of your final post unless you post your own solution.

 

[jtakw]

Hi,

I wonder if OP actually have it's solution, as the suggested formula only addressed part of the question.

May be try this:

Book3.xlsx
	I	J
1	Comments	Status
2	SSM:NAR	Resolved
3	AD:NAR	Resolved
4	SSM:To be queried	Open
5	AD:Open query	Open
6	AD:Added to permanent issue	Permanent Issue
Sheet823

Cell Formulas
Range	Formula
J2:J6	J2	=IF(COUNTIF(I2,"*NAR*"),"Resolved",IF(SUM(COUNTIF(I2,{"*queried*","*query*"})),"Open",IF(COUNTIF(I2,"*permanent*"),"Permanent Issue","")))

 

[sudhansu121]

T

Hi,

I wonder if OP actually have it's solution, as the suggested formula only addressed part of the question.

May be try this:

Book3.xlsx
	I	J
1	Comments	Status
2	SSM:NAR	Resolved
3	AD:NAR	Resolved
4	SSM:To be queried	Open
5	AD:Open query	Open
6	AD:Added to permanent issue	Permanent Issue
Sheet823

Cell Formulas
Range	Formula
J2:J6	J2	=IF(COUNTIF(I2,"*NAR*"),"Resolved",IF(SUM(COUNTIF(I2,{"*queried*","*query*"})),"Open",IF(COUNTIF(I2,"*permanent*"),"Permanent Issue","")))

Cell Formulas
Range	Formula

Sure :)

Thanks

Cell Formulas
Range	Formula