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

[Peter Psych Teacher]

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?

 

[Chip Pearson]

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

 

[Ken Johnson]

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.  Iwant
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