IF formula

[CommonCents]

=IF(AND(ISNUMBER(SEARCH("SIP",D1:D109)),O20>0),"SURCORCLC required", "")


that is the formula I am trying to use, however it will not populate "SURCORCLC required" when it is a "range" of fields (D1:D109)...but when I put one specific cell, it works...any help would be much appreciated...the goal is to find SIP in the entire column of D...thanks.

[TMS]

Try:

=IF(AND(ISNUMBER(MATCH("SIP",D1:D109,0)),O20>0),"SURCORCLC required", "")

Regards

[CommonCents]

No dice. still doesn't work.

[TMS]

Works for me otherwise I wouldn't have posted it.

Did you copy my formula or edit yours?

Upload your workbook please.

Regards

[Richard Buttrey]

Hi,

The SEARCH() function only examines text in a cell. You probably need to use an =MATCH() function perhaps wrapped inside an =INDEX() function. But I don't understand why you're testing for a number when you appear to be wanting to find instances of 'SIP'.

Upload a workbook so that we can see your request in context.

Rgds

[CommonCents]

shucks, you are correct, I copied exactly and it worked...thank you, I appreciate your help, i guess i just tried search and it worked in one cell, now if I wanted to include multiple words, how would that look if you could help...thank you again for the help.

[CommonCents]

and I appologize, but due to the sensitivity of my work, and who I work for I can't post the actual sheet, as mundane and non-classified as it is.

[CommonCents]

the scenario is that i am trying to include the search not just for SIP but for SIP101, SIP201, SIP301, SIP401...

[TMS]

If it's the same prefix, add a wild card (*)

=IF(AND(ISNUMBER(MATCH("SIP*",D1:D109,0)),O20>0),"SURCORCLC required", "")

Regards

[Richard Buttrey]

Hi,

You can use =MATCH("SIP*",D1:D109",False) to find the row with the first occurrence of SIP..., but your mention of trying to find several SIP's suggests that maybe you're trying to extract data and not just identify a single occurrence.

Noting what you've said about sensitivity, nevertheless can't you just disguise all the data and change it to something else. We only want a subset of the data but really need to see the context and know exactly what you want to appear once you've identified the records.

Rgds

[CommonCents]

perfect! thank you much.

[CommonCents]

it' not that the data is all that senstive, it is that I have very stringent network restrictions to uploading nd downloading from confidential servers and computers that I work from.

[TMS]

You're welcome. Thanks for the feedback.