I'm using Excel 2002 SP3

With cell f1 set to the value xyz, the formula

=match(f1,'sheetname'!$a$1:$a$500,0)

returns #N/A

I change this to

=match("xyz",'sheetname'!$a$1:$a$500,0)

and it returns a sensible index for the data.

The lookup function fails in the same way.

Is this a bug (e.g. interpreting f1 in 'sheetname' instead of the current
worksheet), or is there some setting that needs to be tweaked for this to
work (e.g. format assigned to f1)?

Thanks in advance

Jeff