Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return the reference of a range of cells | Excel Discussion (Misc queries) | |||
search within a range of cells? | Excel Discussion (Misc queries) | |||
auto filter doesn't return cells containing the custom search wor. | Excel Worksheet Functions | |||
need formula to search column for a word and return another word | Excel Discussion (Misc queries) | |||
Excel - Formula Query: Search for and Return Value | Excel Worksheet Functions |