Formula that will test text conditions in a single cell

I need a function that will use a column of text values and test these values
to see if one or more of the values exist in a single cell. If it does I need
the function to return true or false.

Ie. cell A1 contains the text "Jim Smith" the B column contains the test
names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
Because Jim is in the cell A1 I would need the function in C1 to return the
value "true". If A1 contained the text "bob smith" then function in C1 would
return the value "false".

In C1, enter:
=IF(SUMPRODUCT(--ISNUM(FIND(B1:B3;$A$1)))>0;TRUE;FALSE)

See example: http://cjoint.com/?eblmKt6Cpa

HTH
--
AP

"Prohock" <Prohock@discussions.microsoft.com> a écrit dans le message de
news:A93983F9-51B9-49D4-B040-91B0C45CFB69@microsoft.com...
> I need a function that will use a column of text values and test these
values
> to see if one or more of the values exist in a single cell. If it does I
need
> the function to return true or false.
>
> Ie. cell A1 contains the text "Jim Smith" the B column contains the test
> names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
> Because Jim is in the cell A1 I would need the function in C1 to return
the
> value "true". If A1 contained the text "bob smith" then function in C1
would
> return the value "false".

There is a typo in the formula, and you might want to use SEARCH as the OP
specified Jim Smith and jim

=SUMPRODUCT(--ISNUMBER(SEARCH(B1:B3,$A$1)))>0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:u7WcOzWVGHA.224@TK2MSFTNGP10.phx.gbl...
> In C1, enter:
> =IF(SUMPRODUCT(--ISNUM(FIND(B1:B3;$A$1)))>0;TRUE;FALSE)
>
> See example: http://cjoint.com/?eblmKt6Cpa
>
> HTH
> --
> AP
>
> "Prohock" <Prohock@discussions.microsoft.com> a écrit dans le message de
> news:A93983F9-51B9-49D4-B040-91B0C45CFB69@microsoft.com...
> > I need a function that will use a column of text values and test these
> values
> > to see if one or more of the values exist in a single cell. If it does I
> need
> > the function to return true or false.
> >
> > Ie. cell A1 contains the text "Jim Smith" the B column contains the test
> > names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is
"jim".
> > Because Jim is in the cell A1 I would need the function in C1 to return
> the
> > value "true". If A1 contained the text "bob smith" then function in C1
> would
> > return the value "false".
>
>

=SUMPRODUCT(COUNTIF(A1,"*"&B1:B3&"*"))>0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Prohock" <Prohock@discussions.microsoft.com> wrote in message
news:A93983F9-51B9-49D4-B040-91B0C45CFB69@microsoft.com...
> I need a function that will use a column of text values and test these
values
> to see if one or more of the values exist in a single cell. If it does I
need
> the function to return true or false.
>
> Ie. cell A1 contains the text "Jim Smith" the B column contains the test
> names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
> Because Jim is in the cell A1 I would need the function in C1 to return
the
> value "true". If A1 contained the text "bob smith" then function in C1
would
> return the value "false".

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$B$3,A1)))+0

A result of 1 means a hit, 0 no hit.

Prohock wrote:
> I need a function that will use a column of text values and test these values
> to see if one or more of the values exist in a single cell. If it does I need
> the function to return true or false.
>
> Ie. cell A1 contains the text "Jim Smith" the B column contains the test
> names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
> Because Jim is in the cell A1 I would need the function in C1 to return the
> value "true". If A1 contained the text "bob smith" then function in C1 would
> return the value "false".

Thanks to everyone for their assistance, it works perfect! One more question,
How would you adapt the formula so that it test any value that is located in
Column B. Currently if I try to test the entire column I get false results
because of blank cells? Ie

"Aladin Akyurek" wrote:

> =ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$B$3,A1)))+0
>
> A result of 1 means a hit, 0 no hit.
>
> Prohock wrote:
> > I need a function that will use a column of text values and test these values
> > to see if one or more of the values exist in a single cell. If it does I need
> > the function to return true or false.
> >
> > Ie. cell A1 contains the text "Jim Smith" the B column contains the test
> > names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
> > Because Jim is in the cell A1 I would need the function in C1 to return the
> > value "true". If A1 contained the text "bob smith" then function in C1 would
> > return the value "false".
>

The sumproduct variants will not work on a entire column, it must be a
specified range, but you can make them large.

=SUMPRODUCT(--(B1:B300<>""),COUNTIF(A1,"*"&B1:B300&"*"))>0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Prohock" <Prohock@discussions.microsoft.com> wrote in message
news:4F57BD03-769E-48E7-BECA-1DD397892455@microsoft.com...
> Thanks to everyone for their assistance, it works perfect! One more
question,
> How would you adapt the formula so that it test any value that is located
in
> Column B. Currently if I try to test the entire column I get false results
> because of blank cells? Ie
>
> "Aladin Akyurek" wrote:
>
> > =ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$B$3,A1)))+0
> >
> > A result of 1 means a hit, 0 no hit.
> >
> > Prohock wrote:
> > > I need a function that will use a column of text values and test these
values
> > > to see if one or more of the values exist in a single cell. If it does
I need
> > > the function to return true or false.
> > >
> > > Ie. cell A1 contains the text "Jim Smith" the B column contains the
test
> > > names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is
"jim".
> > > Because Jim is in the cell A1 I would need the function in C1 to
return the
> > > value "true". If A1 contained the text "bob smith" then function in C1
would
> > > return the value "false".
> >

Is there away to "Ingnore Blank Cells"?

"Bob Phillips" wrote:

> The sumproduct variants will not work on a entire column, it must be a
> specified range, but you can make them large.
>
> =SUMPRODUCT(--(B1:B300<>""),COUNTIF(A1,"*"&B1:B300&"*"))>0
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Prohock" <Prohock@discussions.microsoft.com> wrote in message
> news:4F57BD03-769E-48E7-BECA-1DD397892455@microsoft.com...
> > Thanks to everyone for their assistance, it works perfect! One more
> question,
> > How would you adapt the formula so that it test any value that is located
> in
> > Column B. Currently if I try to test the entire column I get false results
> > because of blank cells? Ie
> >
> > "Aladin Akyurek" wrote:
> >
> > > =ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$B$3,A1)))+0
> > >
> > > A result of 1 means a hit, 0 no hit.
> > >
> > > Prohock wrote:
> > > > I need a function that will use a column of text values and test these
> values
> > > > to see if one or more of the values exist in a single cell. If it does
> I need
> > > > the function to return true or false.
> > > >
> > > > Ie. cell A1 contains the text "Jim Smith" the B column contains the
> test
> > > > names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is
> "jim".
> > > > Because Jim is in the cell A1 I would need the function in C1 to
> return the
> > > > value "true". If A1 contained the text "bob smith" then function in C1
> would
> > > > return the value "false".
> > >
>
>
>

That formula does.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Prohock" <Prohock@discussions.microsoft.com> wrote in message
news:C1379113-D828-46FC-B2C8-C11E0639D0DE@microsoft.com...
> Is there away to "Ingnore Blank Cells"?
>
> "Bob Phillips" wrote:
>
> > The sumproduct variants will not work on a entire column, it must be a
> > specified range, but you can make them large.
> >
> > =SUMPRODUCT(--(B1:B300<>""),COUNTIF(A1,"*"&B1:B300&"*"))>0
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Prohock" <Prohock@discussions.microsoft.com> wrote in message
> > news:4F57BD03-769E-48E7-BECA-1DD397892455@microsoft.com...
> > > Thanks to everyone for their assistance, it works perfect! One more
> > question,
> > > How would you adapt the formula so that it test any value that is
located
> > in
> > > Column B. Currently if I try to test the entire column I get false
results
> > > because of blank cells? Ie
> > >
> > > "Aladin Akyurek" wrote:
> > >
> > > > =ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$B$3,A1)))+0
> > > >
> > > > A result of 1 means a hit, 0 no hit.
> > > >
> > > > Prohock wrote:
> > > > > I need a function that will use a column of text values and test
these
> > values
> > > > > to see if one or more of the values exist in a single cell. If it
does
> > I need
> > > > > the function to return true or false.
> > > > >
> > > > > Ie. cell A1 contains the text "Jim Smith" the B column contains
the
> > test
> > > > > names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is
> > "jim".
> > > > > Because Jim is in the cell A1 I would need the function in C1 to
> > return the
> > > > > value "true". If A1 contained the text "bob smith" then function
in C1
> > would
> > > > > return the value "false".
> > > >
> >
> >
> >

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(IF(B1:B20<>"",B1:B20,-9.99999999999999E+307),A1)))+0

which needs to be confirmed with control+shift+enter, not just with enter.

Prohock wrote:
> Thanks to everyone for their assistance, it works perfect! One more question,
> How would you adapt the formula so that it test any value that is located in
> Column B. Currently if I try to test the entire column I get false results
> because of blank cells? Ie
>
> "Aladin Akyurek" wrote:
>
>
>>=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$1:$B$3,A1)))+0
>>
>>A result of 1 means a hit, 0 no hit.
>>
>>Prohock wrote:
>>
>>>I need a function that will use a column of text values and test these values
>>>to see if one or more of the values exist in a single cell. If it does I need
>>>the function to return true or false.
>>>
>>>Ie. cell A1 contains the text "Jim Smith" the B column contains the test
>>>names (column of test values ) ie. B1 is "bill" B2 is "fred" B3 is "jim".
>>>Because Jim is in the cell A1 I would need the function in C1 to return the
>>>value "true". If A1 contained the text "bob smith" then function in C1 would
>>>return the value "false".
>>