Equations using arrays

Hello,

I'm having some trouble using arrays. The following example will help me explain my problem:

COL A
pen
paper
eraser
pencil

I would like to write a formula along the lines of:

if("pen"=$A$2:$A$5,"Y","N")

My hopes is that because "pen" is contained in the array this function would return "Y" but instead I get #VALUE!. This is what I get when i put the above example in verbatim. Any suggestions?

try this:-

if("pen"=$A$2:$A$5,"Y","N")

this is because as you are using ":" in "$A:$5"

=IF(MATCH("paper",A2:A5,0)>0,"Yes","No")

nikolasm,

For something like this, there is no need for an array formula. Just use the Countif() function:

Please Login or Register  to view this content.

@AndyLitch, if there is no match, the MATCH function will return an error.