Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jeff Melvaine
 
Posts: n/a
Default The match and lookup functions can find literal data but not the same data referenced from a cell

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


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
Jeff Melvaine
 
Posts: n/a
Default


"Dave Peterson" wrote in message
...
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.


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


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


Thanks in advance

Jeff


--

Dave Peterson



  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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.

Jeff Melvaine wrote:

"Dave Peterson" wrote in message
...
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.


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


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


Thanks in advance

Jeff


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't find the right lookup formula for this bankscl Excel Worksheet Functions 4 March 28th 05 06:27 PM
Lookup then Match and insert value from next column Tenacity Excel Worksheet Functions 3 March 4th 05 03:49 AM
find LAST match in column Pantryman Excel Worksheet Functions 7 February 24th 05 12:18 PM
Find a match that;s not exact Phyllis Excel Worksheet Functions 0 November 8th 04 09:12 PM
find LAST match in column Pantryman Excel Worksheet Functions 1 November 5th 04 05:05 PM


All times are GMT +1. The time now is 04:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"