My first guess is that the value in F1 isn't really just "xyz".

If you put this in an empty cell:
=f1="xyz"
do you get true or false?

And another formula that may help:
=len(f1)

I'd look for leading/trailing/multiple embedded spaces.

If you're still having trouble finding the difference, maybe using Chip
Pearson's CellView addin that will help:
http://www.cpearson.com/excel/CellView.htm


Jeff Melvaine wrote:

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

--

Dave Peterson