Search for text arrays within text array

[CO_Hokie]

Hi all,


I have two lists of text, one is single words and the second is text strings and need to figure out if any of the text strings contain any of the list of words.


A quick example would be a list of list of businesses in A1:A8 as below

A1 A family doctors office

A2 A trusting law office, LLC

A3 Fresh grocery store, Inc.

A4 Another grocer

A5 Flowers everywhere

A6 Computer help, LLC

A7 Cup of Joe


and a list of lookup text in cells C1:C3


C1 doctor

C2 grocer

C3 LLC


I want to find any business in the A1:A7 range that has any substrings contained in the C1:C3 range. Doesn't have to return anything fancy, just some type of indication that any of the single words exist within the text string. I can do it for static values, just can't quite get this to work for arrays lookups. Any thoughts and ideas would be much appreciated!

 

[SirJB7]

Hi, CO_Hokie!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Despite of this, you might want to give a look at this file:

https://dl.dropbox.com/u/60558749/Search%20for%20text%20arrays%20within%20text%20array%20%28for%20CO_Hokie%20at%20chandoo.org%29.xlsx

It works fine for any number of test words, even it's desirable that it isn't a huge number, just for easy navigation thru the worksheet.


It works as follows:

a) transposes by formula the C1:C3 list to E1:F1 : =INDIRECTO(DIRECCION(COLUMNA()-4;3;4;1)) -----> in english: =INDIRECT(ADDRESS(COLUMN()-4,3,4,1))

b) E2:G8 : =SI.ERROR(HALLAR(E$1;$A2);"") -----> in english: =IFERROR(SEARCH(E$1,$A2),"")

c) result at column I, I2:I8 : =SI(SUMA(E2:G2)>0;"Yes";"No") -----> in english: =IF(SUM(E2:G2)>0,"Yes","No")


Just advise if any issue.


Regards!


Regards!

 

[NARAYANK991]

Hi ,


Can you try this formula , entered as an array formula , using CTRL SHIFT ENTER :


=MAX(INDEX(IF(IFERROR(FIND(TRANSPOSE(Keywords_Range),Comments_Range),0),1,0),ROW(A1),))


Enter the formula in B1 , and copy down. Where there is no match between any of the entries in Keywords_Range , and the entries in Comments_Range , you should get a 0 ; if there is a match , you should get a 1.


Keywords_Range is the range reference where your keywords are listed , e.g. C1:C3


Comments_Range is the range reference where your comments are listed , e.g. A1:A7


Narayan

 

[CO_Hokie]

Thank you SirJB7, I'll take a look!


NARAYANK991, that worked perfectly!