Search range of cells for word & return value for use in formula?
 

Using Excel, I want to be able to type in a word and have a formula that is
"looking" at that cell return a value (true or false, yes or no, 1 or 2)
based on whether the word I typed is or is not in an array of cells. I want
to then be able to use this value in a formula to print a "yes" or "no". Is
there a simple way to do that?

Search range of cells for word & return value for use in formula?
 

The following formula will return "Found" or "Not Found" depending if
the value in A1 is found in the range C3:C10.

=IF(COUNTIF(C3:C10,A1)0,"Found","Not Found")

If you want either TRUE or FALSE, you can simplify this to

=COUNTIF(C3:C10,A1)0

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 19 Oct 2008 14:40:01 -0700, Peter Psych Teacher <Peter Psych
wrote:

Using Excel, I want to be able to type in a word and have a formula that is
"looking" at that cell return a value (true or false, yes or no, 1 or 2)
based on whether the word I typed is or is not in an array of cells. I want
to then be able to use this value in a formula to print a "yes" or "no". Is
there a simple way to do that?

Search range of cells for word & return value for use in formula?
 

On Oct 20, 8:40*am, Peter Psych Teacher <Peter Psych
wrote:
Using Excel, I want to be able to type in a word and have a formula that is
"looking" at that cell return a value (true or false, yes or no, 1 or 2)
based on whether the word I typed is or is not in an array of cells. *I want
to then be able to use this value in a formula to print a "yes" or "no". *Is
there a simple way to do that?

If A1 is the cell you are typing the word into and B1 is the cell with
the formula "looking" at A1 and C1:L1 is an array of 10 cells each
containing a single word, then you could use...

=IF(A1="","",IF(ISERROR(HLOOKUP(A1,C1:L1,1,0)),"No ","Yes"))

in B1.

Ken Johnson