Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Search Formula
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? -- JPS |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Search Formula
Try it this way:
=IF(COUNT(SEARCH($L$1:$O$1,S374)),"UPDATED",IF(ISN UMBER(SEARCH($L$2:$O $2,S374)),"Pending","")) Hope this helps. Pete On Mar 25, 12:52*pm, JPS wrote: 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? -- JPS |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Search Formula
On Tue, 25 Mar 2008 05:52:00 -0700, JPS wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am in search of formula | Excel Worksheet Functions | |||
I am in search of formula | Excel Discussion (Misc queries) | |||
SEARCH FORMULA | Excel Discussion (Misc queries) | |||
need help with a search formula | Excel Discussion (Misc queries) | |||
Search formula | Excel Worksheet Functions |