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,

 

[Fluff]

What makes you think you are getting a false positive?

 

[MHamid]

I did a manual search for one of the code numbers from the main sheet (06024) against the list of LVID Child and the code number (06024) is not in the list, but based on the formula it is on the list. makes no sense that the formula would show a YES when it should be a NO. I understand that I have other factors in my main formula, but the SUMPRODUCT portion of the code is where it looks like it's bringing over the wrong result.
I would provide the data, but I can't since it's sensitive data.

This is another example, cell BC### has the following string:
TESTING (06553) - 06553;TESTING (06497) - 06497;TESTING (05218) - 05218;TESTING (07996) - 07996;TESTING 06500
The numbers are the codes and the semicolon separates them. The numbers in my example are not in the LVID Child list, but the formula =SUMPRODUCT(--ISNUMBER(SEARCH(CBNA_LVID,$B2))) is showing a result of 122.
*** Actually this result is wrong because it was going past my sheet 2 data LVID Child list. It was looking at blank cells. ****

I've corrected the over 100 result above and now I am getting a #SPILL! error that goes past my main sheet data.

Am I using the wrong formula to do what I need? I need to check the code numbers within the string in my main sheet against a list of codes in another sheet (LVID Child List).

Thank you,

 

[Fluff]

Firstly your formula will never return No.
Does your search range contain any blank cells, or values less than 5 characters?

 

[MHamid]

Firstly your formula will never return No.
Does your search range contain any blank cells, or values less than 5 characters?

I see ...
No there are no blank cells. There will always be data. The range value is mostly 5's. I just noticed 2 that are 4 characters and 1 that is 1 character. So it will have to be an exact match.
I also noticed that the string in my main data in cell BC### will not always have these codes in parenthesis, but it looks like it will always be after a dash.

 

[RoryA]

If you're looking for exact matches to the codes in brackets, maybe use

SEARCH("("&CBNA_LVID&")",BC206)

 

[MHamid]

If you're looking for exact matches to the codes in brackets, maybe use

SEARCH("("&CBNA_LVID&")",BC206)

Still gives me a #SPILL! error

 

[Fluff]

You shouldn't get a spill error with

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

 

[MHamid]

You shouldn't get a spill error with

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

Ha, I am so sorry, you are correct. I am not getting the #SPILL! error.

Some of the code within the cell string is not always between the parenthesis, but it's always after a dash. Any possible way we can do a dash instead of the parenthesis? Or possibly a wildcard to look for a 5 digit code?

 

[Fluff]

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