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