False positive using IF(SUMPRODUCT(--ISNUMBER(SEARCH

[MHamid]

Hello,

I am using the below formula and it is giving me FALSE positives for some items.

=IF($AB206<>"",UPPER(VLOOKUP($L206,Check_YTD!$B:$T,19,0)),IF(SUMPRODUCT(--ISNUMBER(SEARCH(CBNA_LVID,BC206)))>0,"YES",IF($AB206="",VLOOKUP($L206,Prelim_AuditPlan!$B:$K,10,0),"NO")))

The false positive occurs in this section of the formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(CBNA_LVID,BC206))

CBNA_LVID is a list of numbers (some with letters ... numbers in these cells are formatted as text if that makes a difference). Below is a sample of the long list.

LVID Child

C0021

C0036

C0034

C0026

E0129

C0035

C0050

E0372

24118

00312

02587

09220

24119

The within_text portion of the formula goes back to a cell in the main sheet that would look like this: AO Citigroup Global Markets(06024) - 06024. Some cells in column BC for the main sheet may contain multiple codes. The number portion of the within_text cell is what I need searched.

What I am trying to do is see if the code number in cell BC### of the main sheet is within the list of codes in another sheet named range CBNA_LVID. It is giving a false positive for the example code above (06024).

What am I missing in my formula to correct the FALSE positives?


Thank you,

 

[MHamid]

Is there always a space between the dash & the code?

From what I can tell it appears that there is a dash after the 5 digit code number.

 

[Fluff]

First you said the code

it's always after a dash

No your saying the dash is after the code.

 

[MHamid]

First you said the code

No your saying the dash is after the code.

Sorry, didn't mean to say dash. I thought I said space. "There's always a space between the dash and the 5 digit code" ... that's what i thought i said.
This is what the string would look like separated by the semicolon:
TESTING (06553) - 06553;TESTING (06497) - 06497;TESTING (05218) - 05218;TESTING (07996) - 07996;TESTING - 06500

 

[Fluff]

Ok, in that case try

=SUMPRODUCT(--(ISNUMBER(SEARCH("- "&CBNA_LVID,BC206))))