Hello,
I am using the below formula and it is giving me FALSE positives for some items.
The false positive occurs in this section of the formula:
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.
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,
I am using the below formula and it is giving me FALSE positives for some items.
Excel Formula:
=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:
Excel 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,