CODEWITHSUNDEEP
arraysexcel
Alistair Lindsay-Macfadyen
Alistair Lindsay-Macfadyen

Reputation: 135

Searching an Array of Cells in Excel

I am trying to search an array of cells in excel to find if it contains a word to then further evaluate.

so for example; I have named the array (A1:A5) as 'cList'.

A1 = apple
A2 = pear
A3 = orange
A4 = banana
A5 = cherry

I want to 

=SEARCH("pear",cList)

but i keep getting FALSE - which is not true because it is contained in A2. My thought here is that Search cannot be used on an array, because if I instead used 

=SEARCH("pear",A2)

I will get my desired TRUE.

So is there another way to test an array if it contains and answer?

Upvotes: 0

Views: 1079

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34210

SEARCH only searches a single cell. The easiest way to find if a range contains a word is just to use COUNTIF

=COUNTIF($A$1:$A$5,"pear")

This tells you how many matches there are, or to get it as a TRUE/FALSE value

=COUNTIF($A$1:$A$5,"pear")>0

You can also use wildcards, so this would find things like "pearmain" and "prickly pear"

=COUNTIF($A$1:$A$5,"*pear*")>0

Upvotes: 3

JShell
JShell

Reputation: 624

Sounds like a for loop would work fine...

for(int i=1; i<6; i++){ String cell = "A" + i; if(SEARCH("pear",cell) //do things... }

EDIT: Rereading your question, you'd want to adapt this to loop over your array...

Upvotes: 0

Related Questions