Search for reference in an array

D

DoubleZ

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
 
S

ShaneDevenshire

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Alternative to ADDRESS function 1
Finding Text within an Array 2
Function to determine if a cell contains a formula 5
text exist 3
How to set up this array formula? 4
Lookup text in array & return cell reference 5
Array Formula 1
Referencing a named array 1

Top