Value Not Available Error in Vlookup

I'm using the vlookup function to get a number back from a table. I've
created the vlookup properly but keep getting the #N/A with the message -
"Value Not Available Error". The problem is that the value is available.
I've copied the vlookup formula into a number of cells - all come back #N/A.
The puzzle is, I can get the formula to work if I retype the value being
looked up in the table. I've compared formats; I've made sure the table is
in alphabetical order; I can't see any difference from the value being looked
up with the value in the table - they are even the same font.
Any ideas? The table is copied into the worksheet from another workbook.
Thanks for any help.

Hi

when you retype the value being lookup it tends to suggest a problem along
the lines of:
- calculation set to manual - tools / options / calculation -check that it
is set to automatic
- leading or trailing spaces in this value in the lookup value - type in
another cell =LEN(A1) where A1 is the cell reference of your lookup value to
find out how many characters excel sees the cell as having as compared to
how many you think it has
- numbers that aren't really numbers if you're looking up numbers - type in
another cell =ISNUMBER(A1) to test that what you think is a number is
acutally a number
-omitting the 4th parameter (0 or FALSE) for an exact match - although this
would normally raise wrong answers rather than an #NA error

hope this helps
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"thefeokas" <[email protected]> wrote in message
news:[email protected]...
> I'm using the vlookup function to get a number back from a table. I've
> created the vlookup properly but keep getting the #N/A with the message -
> "Value Not Available Error". The problem is that the value is available.
> I've copied the vlookup formula into a number of cells - all come back
> #N/A.
> The puzzle is, I can get the formula to work if I retype the value being
> looked up in the table. I've compared formats; I've made sure the table
> is
> in alphabetical order; I can't see any difference from the value being
> looked
> up with the value in the table - they are even the same font.
> Any ideas? The table is copied into the worksheet from another workbook.
> Thanks for any help.

That did it! I found that there were spaces in the lookup table at the end of
the values. I used Replace... to remove the trailing spaces.
Thanks.

"JulieD" wrote:

> Hi
>
> when you retype the value being lookup it tends to suggest a problem along
> the lines of:
> - calculation set to manual - tools / options / calculation -check that it
> is set to automatic
> - leading or trailing spaces in this value in the lookup value - type in
> another cell =LEN(A1) where A1 is the cell reference of your lookup value to
> find out how many characters excel sees the cell as having as compared to
> how many you think it has
> - numbers that aren't really numbers if you're looking up numbers - type in
> another cell =ISNUMBER(A1) to test that what you think is a number is
> acutally a number
> -omitting the 4th parameter (0 or FALSE) for an exact match - although this
> would normally raise wrong answers rather than an #NA error
>
> hope this helps
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "thefeokas" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using the vlookup function to get a number back from a table. I've
> > created the vlookup properly but keep getting the #N/A with the message -
> > "Value Not Available Error". The problem is that the value is available.
> > I've copied the vlookup formula into a number of cells - all come back
> > #N/A.
> > The puzzle is, I can get the formula to work if I retype the value being
> > looked up in the table. I've compared formats; I've made sure the table
> > is
> > in alphabetical order; I can't see any difference from the value being
> > looked
> > up with the value in the table - they are even the same font.
> > Any ideas? The table is copied into the worksheet from another workbook.
> > Thanks for any help.
>
>
>

you're welcome and thanks for the feedback


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"thefeokas" <[email protected]> wrote in message
news:[email protected]...
> That did it! I found that there were spaces in the lookup table at the end
> of
> the values. I used Replace... to remove the trailing spaces.
> Thanks.
>
> "JulieD" wrote:
>
>> Hi
>>
>> when you retype the value being lookup it tends to suggest a problem
>> along
>> the lines of:
>> - calculation set to manual - tools / options / calculation -check that
>> it
>> is set to automatic
>> - leading or trailing spaces in this value in the lookup value - type in
>> another cell =LEN(A1) where A1 is the cell reference of your lookup value
>> to
>> find out how many characters excel sees the cell as having as compared to
>> how many you think it has
>> - numbers that aren't really numbers if you're looking up numbers - type
>> in
>> another cell =ISNUMBER(A1) to test that what you think is a number is
>> acutally a number
>> -omitting the 4th parameter (0 or FALSE) for an exact match - although
>> this
>> would normally raise wrong answers rather than an #NA error
>>
>> hope this helps
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "thefeokas" <[email protected]> wrote in message
>> news:[email protected]...
>> > I'm using the vlookup function to get a number back from a table. I've
>> > created the vlookup properly but keep getting the #N/A with the
>> > message -
>> > "Value Not Available Error". The problem is that the value is
>> > available.
>> > I've copied the vlookup formula into a number of cells - all come back
>> > #N/A.
>> > The puzzle is, I can get the formula to work if I retype the value
>> > being
>> > looked up in the table. I've compared formats; I've made sure the
>> > table
>> > is
>> > in alphabetical order; I can't see any difference from the value being
>> > looked
>> > up with the value in the table - they are even the same font.
>> > Any ideas? The table is copied into the worksheet from another
>> > workbook.
>> > Thanks for any help.
>>
>>
>>