(v)lookup part of text in cell

[Ted]

Hi.

Can anybody solve this problem in excel?
I want to lookup a part of a word in cells fa searching for john and get a
"responce" like this:

Johndoe | yes
john,doe | yes
doejoHn | yes
samual |
Johnace | yes
pete |

I was thinking it could be possible with using wildcards in the vlookup
function, but I don' t know how. It would be nice if capitals are ignored.

Tia
Ted

[KL]

Hi,

yes, you can use wildcards with vlookup:

=IF(ISERROR(VLOOKUP("*john*",A1,1,0),"","yes")

or you can do this:

=CHOOSE(ISNUMBER(SEARCH("john",A1)+1,"yes","")

or:

=IF(ISNUMBER(SEARCH("john",A1),"yes","")

or:

=--ISNUMBER(SEARCH("john",A1)
and then format cell using the following custom style: [=0]"";"yes"

Regards,
KL


"Ted" <[email protected]> wrote in message
news:[email protected]...
> Hi.
>
> Can anybody solve this problem in excel?
> I want to lookup a part of a word in cells fa searching for john and get a
> "responce" like this:
>
> Johndoe | yes
> john,doe | yes
> doejoHn | yes
> samual |
> Johnace | yes
> pete |
>
> I was thinking it could be possible with using wildcards in the vlookup
> function, but I don' t know how. It would be nice if capitals are ignored.
>
> Tia
> Ted
>
>
>
>

[RagDyer]

Try this:

=IF(ISNA(MATCH("*john*",A1,0)),"","Yes")

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Ted" <[email protected]> wrote in message
news:[email protected]...
> Hi.
>
> Can anybody solve this problem in excel?
> I want to lookup a part of a word in cells fa searching for john and get a
> "responce" like this:
>
> Johndoe | yes
> john,doe | yes
> doejoHn | yes
> samual |
> Johnace | yes
> pete |
>
> I was thinking it could be possible with using wildcards in the vlookup
> function, but I don' t know how. It would be nice if capitals are ignored.
>
> Tia
> Ted
>
>
>
>

[Dave Peterson]

One more:
=if(countif(a1,"*john*")>0,"Yes","")



Ted wrote:
>
> Hi.
>
> Can anybody solve this problem in excel?
> I want to lookup a part of a word in cells fa searching for john and get a
> "responce" like this:
>
> Johndoe | yes
> john,doe | yes
> doejoHn | yes
> samual |
> Johnace | yes
> pete |
>
> I was thinking it could be possible with using wildcards in the vlookup
> function, but I don' t know how. It would be nice if capitals are ignored.
>
> Tia
> Ted

--

Dave Peterson

[Ted]

Thanks guys, lots of sollutions