The match and lookup functions can find literal data but not the same data referenced from a cell

[Jeff Melvaine]

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]

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]

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?
true


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

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

Good thought, but a column of the formula showed no extraneous characters.

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

What I have found is that the problem does not seem to be consistent. I
have jiggled things around enough to get a result since my first posting.
I've also succeeded in discrediting one of my conjectures (see below).

Thanks for the link. The Pearson site looks to be a good source of
information.

Specifying the worksheet name explicitly for f1 does not improve matters.

 

[Dave Peterson]

Are xyz numbers?

If they are, then 123 and "123" won't match. (but that test would have failed,
too.)

Can you look at those a1:a500 and pick out the cell that should match the value
in F1.

Then try
=f1='sheetname'!$a$333
to see what happens.