software

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 quot;Jim Smithquot; the B column contains the test
names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is quot;jimquot;.
Because Jim is in the cell A1 I would need the function in C1 to return the
value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1 would
return the value quot;falsequot;.

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

See example: cjoint.com/?eblmKt6Cpa

HTH
--
AP

quot;Prohockquot; gt; a écrit dans le message de
...
gt; I need a function that will use a column of text values and test these
values
gt; to see if one or more of the values exist in a single cell. If it does I
need
gt; the function to return true or false.
gt;
gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains the test
gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is quot;jimquot;.
gt; Because Jim is in the cell A1 I would need the function in C1 to return
the
gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1
would
gt; return the value quot;falsequot;.
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)))gt;0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ardus Petusquot; gt; wrote in message
...
gt; In C1, enter:
gt; =IF(SUMPRODUCT(--ISNUM(FIND(B1:B3;$A$1)))gt;0;TRUE;FALSE)
gt;
gt; See example: cjoint.com/?eblmKt6Cpa
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Prohockquot; gt; a écrit dans le message de
gt; ...
gt; gt; I need a function that will use a column of text values and test these
gt; values
gt; gt; to see if one or more of the values exist in a single cell. If it does I
gt; need
gt; gt; the function to return true or false.
gt; gt;
gt; gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains the test
gt; gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is
quot;jimquot;.
gt; gt; Because Jim is in the cell A1 I would need the function in C1 to return
gt; the
gt; gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1
gt; would
gt; gt; return the value quot;falsequot;.
gt;
gt;
=SUMPRODUCT(COUNTIF(A1,quot;*quot;amp;B1:B3amp;quot;*quot;))gt;0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Prohockquot; gt; wrote in message
...
gt; I need a function that will use a column of text values and test these
values
gt; to see if one or more of the values exist in a single cell. If it does I
need
gt; the function to return true or false.
gt;
gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains the test
gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is quot;jimquot;.
gt; Because Jim is in the cell A1 I would need the function in C1 to return
the
gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1
would
gt; return the value quot;falsequot;.
=ISNUMBER(LOOKUP(9.99999999999999E 307,SEARCH($B$1 :$B$3,A1))) 0

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

Prohock wrote:
gt; I need a function that will use a column of text values and test these values
gt; to see if one or more of the values exist in a single cell. If it does I need
gt; the function to return true or false.
gt;
gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains the test
gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is quot;jimquot;.
gt; Because Jim is in the cell A1 I would need the function in C1 to return the
gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1 would
gt; return the value quot;falsequot;.

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

quot;Aladin Akyurekquot; wrote:

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

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

=SUMPRODUCT(--(B1:B300lt;gt;quot;quot;),COUNTIF(A1,quot;*quot;amp;B1:B300amp;quot;*quot;))gt;0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Prohockquot; gt; wrote in message
...
gt; Thanks to everyone for their assistance, it works perfect! One more
question,
gt; How would you adapt the formula so that it test any value that is located
in
gt; Column B. Currently if I try to test the entire column I get false results
gt; because of blank cells? Ie
gt;
gt; quot;Aladin Akyurekquot; wrote:
gt;
gt; gt; =ISNUMBER(LOOKUP(9.99999999999999E 307,SEARCH($B$1 :$B$3,A1))) 0
gt; gt;
gt; gt; A result of 1 means a hit, 0 no hit.
gt; gt;
gt; gt; Prohock wrote:
gt; gt; gt; I need a function that will use a column of text values and test these
values
gt; gt; gt; to see if one or more of the values exist in a single cell. If it does
I need
gt; gt; gt; the function to return true or false.
gt; gt; gt;
gt; gt; gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains the
test
gt; gt; gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is
quot;jimquot;.
gt; gt; gt; Because Jim is in the cell A1 I would need the function in C1 to
return the
gt; gt; gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1
would
gt; gt; gt; return the value quot;falsequot;.
gt; gt;
Is there away to quot;Ingnore Blank Cellsquot;?

quot;Bob Phillipsquot; wrote:

gt; The sumproduct variants will not work on a entire column, it must be a
gt; specified range, but you can make them large.
gt;
gt; =SUMPRODUCT(--(B1:B300lt;gt;quot;quot;),COUNTIF(A1,quot;*quot;amp;B1:B300amp;quot;*quot;))gt;0
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Prohockquot; gt; wrote in message
gt; ...
gt; gt; Thanks to everyone for their assistance, it works perfect! One more
gt; question,
gt; gt; How would you adapt the formula so that it test any value that is located
gt; in
gt; gt; Column B. Currently if I try to test the entire column I get false results
gt; gt; because of blank cells? Ie
gt; gt;
gt; gt; quot;Aladin Akyurekquot; wrote:
gt; gt;
gt; gt; gt; =ISNUMBER(LOOKUP(9.99999999999999E 307,SEARCH($B$1 :$B$3,A1))) 0
gt; gt; gt;
gt; gt; gt; A result of 1 means a hit, 0 no hit.
gt; gt; gt;
gt; gt; gt; Prohock wrote:
gt; gt; gt; gt; I need a function that will use a column of text values and test these
gt; values
gt; gt; gt; gt; to see if one or more of the values exist in a single cell. If it does
gt; I need
gt; gt; gt; gt; the function to return true or false.
gt; gt; gt; gt;
gt; gt; gt; gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains the
gt; test
gt; gt; gt; gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is
gt; quot;jimquot;.
gt; gt; gt; gt; Because Jim is in the cell A1 I would need the function in C1 to
gt; return the
gt; gt; gt; gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function in C1
gt; would
gt; gt; gt; gt; return the value quot;falsequot;.
gt; gt; gt;
gt;
gt;
gt;

=ISNUMBER(LOOKUP(9.99999999999999E 307,SEARCH(IF(B 1:B20lt;gt;quot;quot;,B1:B20,-9.99999999999999E 307),A1))) 0

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

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

That formula does.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Prohockquot; gt; wrote in message
...
gt; Is there away to quot;Ingnore Blank Cellsquot;?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; The sumproduct variants will not work on a entire column, it must be a
gt; gt; specified range, but you can make them large.
gt; gt;
gt; gt; =SUMPRODUCT(--(B1:B300lt;gt;quot;quot;),COUNTIF(A1,quot;*quot;amp;B1:B300amp;quot;*quot;))gt;0
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Prohockquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thanks to everyone for their assistance, it works perfect! One more
gt; gt; question,
gt; gt; gt; How would you adapt the formula so that it test any value that is
located
gt; gt; in
gt; gt; gt; Column B. Currently if I try to test the entire column I get false
results
gt; gt; gt; because of blank cells? Ie
gt; gt; gt;
gt; gt; gt; quot;Aladin Akyurekquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =ISNUMBER(LOOKUP(9.99999999999999E 307,SEARCH($B$1 :$B$3,A1))) 0
gt; gt; gt; gt;
gt; gt; gt; gt; A result of 1 means a hit, 0 no hit.
gt; gt; gt; gt;
gt; gt; gt; gt; Prohock wrote:
gt; gt; gt; gt; gt; I need a function that will use a column of text values and test
these
gt; gt; values
gt; gt; gt; gt; gt; to see if one or more of the values exist in a single cell. If it
does
gt; gt; I need
gt; gt; gt; gt; gt; the function to return true or false.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Ie. cell A1 contains the text quot;Jim Smithquot; the B column contains
the
gt; gt; test
gt; gt; gt; gt; gt; names (column of test values ) ie. B1 is quot;billquot; B2 is quot;fredquot; B3 is
gt; gt; quot;jimquot;.
gt; gt; gt; gt; gt; Because Jim is in the cell A1 I would need the function in C1 to
gt; gt; return the
gt; gt; gt; gt; gt; value quot;truequot;. If A1 contained the text quot;bob smithquot; then function
in C1
gt; gt; would
gt; gt; gt; gt; gt; return the value quot;falsequot;.
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;