Array formula

[Sony2011]

dear

I created a formula that if A1 match any one in the range, then return me yes; else, no.

But, it seems the formula only refers to cell B1 only, instead of the data in the range (B1:B5). it means only if B1 = apple, then the formula works and return me, Yes. What's wrong? I have done the CTRL+SHIFT+ENTER to bracket the array. but it does not help Please help

=if(A1=$B$1:$B$5,"yes","no")

A1 = apple

B1= orange
B2 = grape
B3 = apple
B4 = pears
B5 = banana

Problem, Thank you

 

[Jonmo1]

Welcome to the board...

You can't compare one cell to Several Other cells with just a simple IF like that..

Try Countif..

=IF(COUNTIF($B$1:$B$5,A1)>0,"yes","no")

Hope that helps.

 

[Jonmo1]

Also, even though this may seem more complicated, it will actually be more efficient..

=IF(ISNUMBER(MATCH(A1,$B$1:$B$5,0)),"yes","no")


This is more efficient because Countif must evaluate EVERY cell in $B$1:$B$5,
While Match only has to evaluate cells untill it finds what it's looking for, then it stops.

 

[Sony2011]

Thank you!

 

[Jonmo1]

Glad to help.