Searching an Array of Cells in Excel

Question

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?

Tom Sharpe · Accepted Answer · 2015-06-12 13:38:23Z

3

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

answered Jun 12, 2015 at 13:38

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alistair Lindsay-Macfadyen Over a year ago

completely forgot about COUNTIF() I knew it but I guess I was trying not to use yet another IF(), but it does the trick perfectly. Thanks Tom

JShell · Accepted Answer · 2015-06-12 13:24:23Z

0

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

answered Jun 12, 2015 at 13:24

JShell

6243 gold badges7 silver badges24 bronze badges

4 Comments

Vincent De Smet Over a year ago

not really, the built in excel functions are way faster than re-implementing them in VBA... If you study CS, you should know to program against an api and use it's available methods instead of re-implementing. All you know you're asking every cell value, but Excel might store the data in some kind of hashmap and work much faster through the functions it exposes for you.

JShell Over a year ago

Absolutely, but another user covered that approach.

Vincent De Smet Over a year ago

then your answer should include the fact that it will be slower, it would be a good quick/lazy fix or if someone wants to practice. You do say it would be "fine"

JShell Over a year ago

You're really over analyzing this. The OP determined the better answer on his own, no need to baby him

Collectives™ on Stack Overflow

Searching an Array of Cells in Excel

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related