Why do two formulas need to take the place of one formula? OR why does "OR" not work?

[Eloise T]

When I composed the following, I only needed *Diag* in the formula...and it worked.

IF(AND(H7=30,IFERROR(SEARCH("*Diag*",J7)>0,0)),40, √

Months later I needed to add *ware*.

When using the formula:

IF(AND(H7=30,IFERROR(SEARCH(OR("*Diag*","*ware*"),J7)>0,0)),40, X

it didn't yield the correct result(s).

But when I removed the OR function and duplicated the 1st line:
IF(AND(H7=30,IFERROR(SEARCH("*Diag*",J7)>0,0)),40, √
IF(AND(H7=30,IFERROR(SEARCH("*ware*",J7)>0,0)),40, √

it worked. Why?

 

[Khalid NGO]

Hi,

OR function will not work the way you are trying, you can nest another same formula, but it will become lengthy with addition of more keywords.

An alternate with COUNTIFS:

=IF(SUM(COUNTIFS(J7,{"*diag*","*ware*"},H7,30)),40,"...")

Add as many keywords as you need within the {array}.

Regards,

 

[Eloise T]

...and this would be an equivalent to your formula?
=IF(AND(H7=30,AND(ISNUMBER(SEARCH({"*diag*","*ware*"},J7)))),40,

=IF(SUM(COUNTIFS(J7,{"*diag*","*ware*"},H7,30)),40,"...")

 

[Hui]

Eloise

If you look at your original formula:
IF(AND(H7=30,IFERROR(SEARCH(OR("*Diag*","*ware*"),J7)>0,0)),40,

select the red part and press F9
It will evaluate to #VALUE!

That is because OR() is expecting a logical expression inside
eg OR(A1>1, B1<10)
means if either A1 > 1 or B1 < 10 the OR will return True
if Both are True it will return True
If Both are not true OR will return False

So the use of OR() inside Search() is at best going to lookl for either True or False, which isn't what you wanted

I hope you understand Khalid's solution

 

[NARAYANK991]

Hi Eloise ,

I think you are looking for this as the equivalent :

=IF(AND(H7 = 30, OR(ISNUMBER(SEARCH({"*Diag*","*Ware*"}, J7)))), 40, 0)

entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[Khalid NGO]

Hi Narayan Sir,

I think CSE not necessary.

Regards,

 

[Khalid NGO]

=IF(AND(H7=30,AND(ISNUMBER(SEARCH({"*diag*","*ware*"},J7)))),40,

Hi Eloise T,

* (wildcard) is not necessary for Search function, you can get the same result without *.

Regards,

 

[Eloise T]

Hui and Narayan and Khalid,

A big thanks to all three of you. I'm always learning something from you guys.

Khalid, You are correct. C+S+E turned out not to be needed for accuracy. Though I'm confused why wildcard(*) is not necessary...I'll have to experiment. The "phrases" I'm trying to catch are "diagnosis and diagnostic" and "Firmware upgrade and Software upgrade."

Eloise

 

[Khalid NGO]

I'm confused why wildcard(*) is not necessary...I'll have to experiment.

Hi,

More you experiment more you learn.

In this case search function does not requires *, as you have defined the string to be searched.

SEARCH function is looking for e.g. "Diag" if found, it will return TRUE, else FALSE.

Though there is no harm to include *

Regards,