Vlookup error for dynamic lookup value 1

[swaybright]

The formula:
=VLOOKUP(B4,$D$2:$Z$95,13,FALSE)
is returning the #N/A error

if I replace B4 with the value in cell B4, the function returns the expected value.

I have XL version 2002(6789.6714)SP3. I have noticed that this error occurs for some users, but not others.

Does anyone know how to correct it. I apologize if this has been batted around before; please point me in the right direction.

Shane

 

[SkipVought]

Hi,

What is the value in B4?

What KIND of values are in column D?

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[AnotherHiggins]

Most likely there is a string in B4 and a numbers in column D - or vice versa.

There is a big difference between numbers and strings that look like numbers.

Try this: in an empty cell, type in
[tab]=isnumber(B4)

Does that return TRUE or FALSE?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

Also just try a simple =LOOKUP_REF=Value_You_Can_See and see if you get TRUE. If not then they are not the same value. Often happens with numeric data that is the result of a calculation, eg 0.333 <> 1/3, even though both may appear as 0.333 on your screen.

In the case of text it is often rogue spaces or html characters that will trip you up in whcih case you need to do some data cleansing.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[swaybright]

The value in B4 is 1 (not calculated) the values in D are are integers (1 to 48) all cells are formatted as general.

John, you hit it right on the head. The lookup value is a string and the values in the first array column are numbers. I employed the Value() function to correct the problem. Thanks!!


I did some more digging since posting and found sometimes floating point errors can give the same symptoms. The following link describes this and the work around using the Trunc() function (it works for my problem as well):

http://support.microsoft.com/kb/315961/en-us

Thanks for the help guys!

Shane