Confirm if text string exists in cell by searching a named range

[SueG]

HI All,

I'm am trying to become awesome, but it is trying my patience. I have a named range of cells, each cell containing various text strings - in my world: plan codes and plan names. Example:

BGV1
BGVL
BRGV
Cornerstone RHRA Marker
Cornerstone Trad Marker
CRHR
CTRD​

So I have another column of messy raw data. I need to search each cell of THAT column to see if any part the cell text contain any of the text values in the named range. It can simply return true or false. I've been driving myself crazy. One this works, I intend to record if to add to an existing macro. THANK YOU!

 

[Luke M]

Hi SueG,

Here's a formula you could use:
=SUMPRODUCT(1*ISNUMBER(MATCH("*"&PlanCodes&"*",SearchRange,0)))>0

Use your own named ranges as appropriate. Returns True/False if any value from PlanCodes is found within range. Remove the ">0" at end if you want the count of how many were found.

 

[SueG]

Thank you, this seemed to work fine. But, I have a question, if you don't mind. I left in the ">0" at the end, and it returns true/false, so I thought I was good. But if I take it out, it is returning 1, 0 - and 2! I see now that there can be duplicate strings in different cells of the named range - even though each cell in the range is has unique content. Is there a way to modify this formula so a match is returned only if the entire string in the range cell is found in the cell I'm looking at? Not just any string within each cell? I think I may get a false "true" if is not matching on entire range cell contents.. if that makes sense...

Example - 2 cells in my named range:

Cornerstone RHRA Marker
Cornerstone Trad Marker

The cell I am looking at:
"Cornerstone RHRA Marker" ​

I only want a TRUE (or 1) if my cell contains "Cornerstone RHRA Marker"not just because it contains 'Cornerstone'

Thanks, I am learning!!

​

 

[NARAYANK991]

Hi Sue ,

Can you upload a sample workbook ?

Narayan

 

[SueG]

Sure! 1st tab is the messy raw data that comes down from SAP (our system of record). It is an error report with no uniform formatting. Each error has a name line, then 2 error lines underneath it for each error - one of those lines in each set has the plan code and the other line has the plan text. There is a named range of plan codes and plan texts on the 2nd tab. I need to see if any of those values (entire value of cell) exist anywhere in each of the cells of column A on the 1st tab. I have done it with a long array formula before, but I thought using a named ranmge would be cleaner, and easier to maintain, if it could be done. Any suggestions appreciated!!

 

[NARAYANK991]

Hi Sue ,

Will this work ?

Narayan

 

[SueG]

YES - I think that is working fine!! I had done something like that before, although I see your formula has parentheses around it which I believe makes it an array? I have to be careful though, if I click on the array formula and hit enter - the parens goes away. I need to learn more about arrays! Thank you so so much!!