Help with Search Formula

[JPS]

I have a formula +IF(COUNT(SEARCH($L$1:$O$1,S374)),"UPDATED"," ") that addes
the comment UPDATED when the value in L1 - O1 is found. I need to add an or
to this formula to say if the value found in L2 - O2 is found add the comment
PENDING. How can I accomplish this in a single IF(OR) statement?

 

[Pete_UK]

Try it this way:

=IF(COUNT(SEARCH($L$1:$O$1,S374)),"UPDATED",IF(ISNUMBER(SEARCH($L$2:$O
$2,S374)),"Pending",""))

Hope this helps.

Pete

 

[Ron Rosenfeld]

I have a formula +IF(COUNT(SEARCH($L$1:$O$1,S374)),"UPDATED"," ") that addes
the comment UPDATED when the value in L1 - O1 is found. I need to add an or
to this formula to say if the value found in L2 - O2 is found add the comment
PENDING. How can I accomplish this in a single IF(OR) statement?

To what is your formula "adding" the word UPDATED?

In Excel, adding usually refers to arithmetic operations and, as a stand-alone
formula, your "+" sign is unnecessary.

A stand-alone formula will return a result; but if it were part of a larger
formula, it might be able to concatenate.

If you want to have the formula return one value or the other, you could use a
nested IF statement, and the outermost should be the priority (in case values
are found in both ranges.

If you want to return both UDATED and PENDING concatenated together, in case
the value in S374 is found in both ranges, then use two concatenated IF
statements.

Also, you could probably eliminate the SEARCH function by using COUNTIF and
wild cards.

Finally, unless you have some special purpose for returning a <space> if the
match is not present, I would recommend returning a null length string.

e.g. (not tested)

=IF(COUNTIF(rng1, "*"&S374&"*"),"UPDATED","")

=IF(COUNTIF(rng1,"*"&S374&"*"),
"UPDATED",IF(COUNTIF(rng2,"*"&S374&"*"),"PENDING","")

--ron