I am trying to search a column of data for string matches as part of a formula but I can't get the array search function to work properly.

I put the data I want to search for in cell C4 and the column of data I want to search in B4:B9.

So I am using this formula: =SEARCH(C4,B4:B9)
I then press ctrl+shift+enter and the curly brackets appear at each end of the formula

The problem is, the formula still only searches the first cell in the array, not the whole array. So if I had the following in column B:

a
b
c
d
e
f

and I searched for a, the formula would return 1 but if I search for any of the other letters, it returns #value, which is a pain. If someone could point out what I am doing wrong I would be much obliged.