Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for reference in an array
In Excel 2007, i need to search for a certain word within an array. For
example, I have array A1:A10. Each cell in this array contains a word. In Cell B10 I have a word. If the word in B10 is the same as any of the words in the array then I need then I need an IF statement to return true. If it is not contained in the array, I need it to return false. As far as I know, the Search function online applies to searching for a certain value within one cell, not multiple cells. Thanks for any help, DoubleZ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for reference in an array
One way:
=COUNTIF(A1:A10,B10)0 -- Biff Microsoft Excel MVP "DoubleZ" wrote in message ... In Excel 2007, i need to search for a certain word within an array. For example, I have array A1:A10. Each cell in this array contains a word. In Cell B10 I have a word. If the word in B10 is the same as any of the words in the array then I need then I need an IF statement to return true. If it is not contained in the array, I need it to return false. As far as I know, the Search function online applies to searching for a certain value within one cell, not multiple cells. Thanks for any help, DoubleZ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for reference in an array
Fantastic. Thank you.
"T. Valko" wrote: One way: =COUNTIF(A1:A10,B10)0 -- Biff Microsoft Excel MVP "DoubleZ" wrote in message ... In Excel 2007, i need to search for a certain word within an array. For example, I have array A1:A10. Each cell in this array contains a word. In Cell B10 I have a word. If the word in B10 is the same as any of the words in the array then I need then I need an IF statement to return true. If it is not contained in the array, I need it to return false. As far as I know, the Search function online applies to searching for a certain value within one cell, not multiple cells. Thanks for any help, DoubleZ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for reference in an array
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "DoubleZ" wrote in message ... Fantastic. Thank you. "T. Valko" wrote: One way: =COUNTIF(A1:A10,B10)0 -- Biff Microsoft Excel MVP "DoubleZ" wrote in message ... In Excel 2007, i need to search for a certain word within an array. For example, I have array A1:A10. Each cell in this array contains a word. In Cell B10 I have a word. If the word in B10 is the same as any of the words in the array then I need then I need an IF statement to return true. If it is not contained in the array, I need it to return false. As far as I know, the Search function online applies to searching for a certain value within one cell, not multiple cells. Thanks for any help, DoubleZ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for reference in an array
Hi,
The following array formula works also: =SUM(--(A1:A10=B10))0 However, the advantage of countif is it supports wildcards: =COUNTIF(A1:A10,"*"&B10&"*")0 This would count the word Green anywhere in any of the cells in A1:A10 Red & Green The Greening of America .... -- Thanks, Shane Devenshire "DoubleZ" wrote: In Excel 2007, i need to search for a certain word within an array. For example, I have array A1:A10. Each cell in this array contains a word. In Cell B10 I have a word. If the word in B10 is the same as any of the words in the array then I need then I need an IF statement to return true. If it is not contained in the array, I need it to return false. As far as I know, the Search function online applies to searching for a certain value within one cell, not multiple cells. Thanks for any help, DoubleZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search macro with array | New Users to Excel | |||
Please help on array search & result | Excel Worksheet Functions | |||
search array | Excel Worksheet Functions | |||
Defining an array to search by | Excel Discussion (Misc queries) | |||
Text Search in an Array | Excel Discussion (Misc queries) |