Searching for string in variable array

[hypedaj]

Hi all:
Thanks in advance for your help.

I have the following issue:
I'm trying to search for string "Intel" in an array A1:A100
However, in the array, "Intel" doesn't always appear as "Intel", sometimes it's "abcIntel" or "Intelproject1" or " Intel xyz"

I've used the address function and tried to combine it with the FIND/Search function, but with no success.

Any thoughts on how to set this up without VBA?

Thanks!

 

[Aladin Akyurek]

Hi all:
Thanks in advance for your help.

I have the following issue:
I'm trying to search for string "Intel" in an array A1:A100
However, in the array, "Intel" doesn't always appear as "Intel", sometimes it's "abcIntel" or "Intelproject1" or " Intel xyz"

I've used the address function and tried to combine it with the FIND/Search function, but with no success.

Any thoughts on how to set this up without VBA?

Thanks!

Try, for example...

=LOOKUP(9.99999999999999E+307,SEARCH(E1,$A$1:$A$100),$A$1:$A$100)

where E1 houses a search value like Intel.

 

[Ron Coderre]

With
A1:A100 containing a list of text values (blanks allowed)
and
C1: (a text value to find in that list.....eg Intel)

This formula returns the contents of the list cell containing the C1 text
(or "no match")

B1: =IF(COUNTIF(A1:A100,"*"&C1&"*"),INDEX(A1:A100,
MATCH("*"&C1&"*",A1:A100,0)),"no match")

Is that something you can work with?

 

[Kenneth Hobson]

Aladin's method will find the last match.

These methods find the first match, not case sensitive. I prefer method 2 or like Ron's method 3.
=INDIRECT(ADDRESS(MATCH("*" & E1 & "*",$A$1:$A$100,0),1))
=OFFSET($A$4, MATCH("*" & E1 & "*",$A$4:$A$100,0)-1,0)
=INDEX(A1:A100,MATCH("*" & E1 & "*",A1:A100,0),1)