Why doesn't my SEARCH function work between spreadsheets?

I collect payroll data from a variety of different sources; names of
personnel are entered in a variety of ways, e.g. Mr David Moore, David
Moore, Dave Moore, D Moore, Moore D, - with and without periods, with
and without spaces. So trying to match up records is something of a
nightmare!

I have met this challenge by creating an additional column in my
worksheet/spreadsheet for the Surname only with the intention of using
the SEARCH function e.g. =SEARCH(O30,'[Payroll.xls]631131'!$B$8:$B
$160). You will see that this example takes the search string from
my working spreadsheet and searches a field in another (open)
spreadsheet but always returns "#VALUE!" even when I know there is a
match. The same instruction works if in the same worksheet but NOT
between worksheets in the same spreadsheet.

Is this a limitation of the function, or am I doing something wrong?

Appreciate any help.

Regards,
Dave Moore