Search for a "substring" within an Array of Strings

[voryzover]

Hi everyone

First let me express my grattitude for everyone taking their time to answer questions on this board. At least personnally speakig, this board is extremely valuable.

On to question: I know this must be a piece of cake for the experts.

I wish to search for a substring within an array of strings and just return back an indication that the substring exists withing that array.

so for instance let the substring be "rang"
and let the array be
{"apple","orange","lemon"}.
I wish to put a single command line next to my substring and wish to search through that range and return a key code if it exists. And then I will repeat the same search for other substrings. For the moment being, I don't care too much about the number of occurences or the exact cell address where they occur. All I wish to know is whether they exist in that list or not.

So it looks to me it is like a SEARCH command but except on a range of text (Rather than a single text) with an array formula. I have tried MATCH but my case is not an exact match either.

Any help will be greatly appreciated.

Thanks

[Yogi Anand]

Have you tried using

=SEARCH("*rang*",A1,1) .... if your array is in cell A1

Regards!

[Asala42]

Hey,

This would give you a yes or no on whether or not "rang" appears anywhere in your text:

Assuming range A1:A3 (containing "apple","orange", and "lemon" respectively).

=IF(COUNTIF(A1:A3,"*rang*")>0,"yes","no")

Adam

[Asala42]

Heh, you were faster on the draw Yogi

Adam

[voryzover]

Yogi,
Thanks for your reply.
No. The array is huge and is located at a range of cells (rather than a single cell).

[voryzover]

Asala,
Yours worked out perfectly, I knew this was easy. Thanks a lot guys.

[ralegg]

How would you go about having it return the value you were checking for in the cell instead of the CHECKIF statement's true/false. Preferably not with VBA macros, I know it's easier but you can't undo it as easily if you mess up.

[Domenic]

Originally Posted by ralegg

How would you go about having it return the value you were checking for in the cell instead of the CHECKIF statement's true/false. Preferably not with VBA macros, I know it's easier but you can't undo it as easily if you mess up.

Assuming that A2:A10 contains the array of strings, and C2 contains the substring of interest, try...

=IF(ISNUMBER(MATCH("*"&C2&"*",$A$2:$A$10,0)),C2,"")

Hope this helps!

[ralegg]

How about if I am looking for a range of numbers in that array.
For example:

Array1: 10, 40, 63, 78, 112, 560

and I want the number that is between 75 and 100. How would I do this? Also, how does it react if there was an extra 90 in the array and there were potentially 2 numbers within the range?

Thanks!

[ralegg]

Actually, I figured it out using a HLookup formula, thank you though Domenic because it took looking at your formula for me to figure out how to come up with the one that I needed!

BV206=HLOOKUP(150,AK206:BT206,1,TRUE)

BW206=HLOOKUP(420,AK206:BT206,1,TRUE)

BX206=IF(AND(BV206<=150,BV206>=61,BW206<=420,BW206>=300),"Viable","Non-Viable")

(Cell Number=Formula)