Vlookup of text returning "#Value"
 

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) file from so I could perform some analysis on the
smaller file. I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. The lookup_value that I am trying
to match is text. I've already sorted then tried CLEANing and TRIMming to no
avail. I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string.
But it seems arbitrary since some lookups are returned correctly and others
are not.

Vlookup of text returning "#Value"
 

Please post examples of your data, both the lookup value, and the value you
are trying to match to. Otherwise, all we can suggest is what you already
know, that there may be possible leading characters that are messing things
up.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mmm206" wrote:

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) file from so I could perform some analysis on the
smaller file. I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. The lookup_value that I am trying
to match is text. I've already sorted then tried CLEANing and TRIMming to no
avail. I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string.
But it seems arbitrary since some lookups are returned correctly and others
are not.

Vlookup of text returning "#Value"
 

sorry, but a little unfamiliar with this forum. Where can I post to?
Update on the issue. I also did some additional troubleshooting. I saw that
the smallest string length giving me this issue was 256 char, the longest
string that worked was 254. I tried to trim the text using LEFT(value, 255),
now the vlookup returns #N/A error. not sure if this is moving in the right
direction.

"Luke M" wrote:

Please post examples of your data, both the lookup value, and the value you
are trying to match to. Otherwise, all we can suggest is what you already
know, that there may be possible leading characters that are messing things
up.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mmm206" wrote:

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) file from so I could perform some analysis on the
smaller file. I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. The lookup_value that I am trying
to match is text. I've already sorted then tried CLEANing and TRIMming to no
avail. I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string.
But it seems arbitrary since some lookups are returned correctly and others
are not.

Vlookup of text returning "#Value"
 

The formula is: =VLOOKUP(K8,[other file on my desktop]$F:$N,9,FALSE). I am
using the text in K8 to lookup what should be the same text in the other file
on my desk top, and then returning the value from column N (the 9th colum of
data in the lookup range).

"mmm206" wrote:

sorry, but a little unfamiliar with this forum. Where can I post to?
Update on the issue. I also did some additional troubleshooting. I saw that
the smallest string length giving me this issue was 256 char, the longest
string that worked was 254. I tried to trim the text using LEFT(value, 255),
now the vlookup returns #N/A error. not sure if this is moving in the right
direction.

"Luke M" wrote:

Please post examples of your data, both the lookup value, and the value you
are trying to match to. Otherwise, all we can suggest is what you already
know, that there may be possible leading characters that are messing things
up.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mmm206" wrote:

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) file from so I could perform some analysis on the
smaller file. I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. The lookup_value that I am trying
to match is text. I've already sorted then tried CLEANing and TRIMming to no
avail. I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string.
But it seems arbitrary since some lookups are returned correctly and others
are not.

Vlookup of text returning "#Value"
 

If that other file is not open then your formula should include the
full path, filename and sheet name followed by ! before the $F:$N
term. It is not clear from your example. The square brackets should
surround the [filename.xls], and if you have any spaces you should
surround the 'path[filename.xls]sheetname' with apostrophes.

Hope this helps.

Pete

On Apr 6, 6:20*pm, mmm206 wrote:
The formula is: =VLOOKUP(K8,[other file on my desktop]$F:$N,9,FALSE). *I am
using the text in K8 to lookup what should be the same text in the other file
on my desk top, and then returning the value from column N (the 9th colum of
data in the lookup range).



"mmm206" wrote:
sorry, but a little unfamiliar with this forum. *Where can I post to?
Update on the issue. *I also did some additional troubleshooting. I saw that
the smallest string length giving me this issue was 256 char, the longest
string that worked was 254. *I tried to trim the text using LEFT(value, 255),
now the vlookup returns #N/A error. *not sure if this is moving in the right
direction.

"Luke M" wrote:

Please post examples of your data, both the lookup value, and the value you
are trying to match to. Otherwise, all we can suggest is what you already
know, that there may be possible leading characters that are messing things
up.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"mmm206" wrote:

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) *file from so I could perform some analysis on the
smaller file. *I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. *The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. *The lookup_value that I am trying
to match is text. *I've already sorted then tried CLEANing and TRIMming to no
avail. *I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string. *
But it seems arbitrary since some lookups are returned correctly and others
are not.- Hide quoted text -

- Show quoted text -

Vlookup of text returning "#Value"
 

You are right, the path is correct, I had just taken it out to save space. I
found a work around for this problem, so I should be good for now. Thanks
for your help!

"mmm206" wrote:

sorry, but a little unfamiliar with this forum. Where can I post to?
Update on the issue. I also did some additional troubleshooting. I saw that
the smallest string length giving me this issue was 256 char, the longest
string that worked was 254. I tried to trim the text using LEFT(value, 255),
now the vlookup returns #N/A error. not sure if this is moving in the right
direction.

"Luke M" wrote:

Please post examples of your data, both the lookup value, and the value you
are trying to match to. Otherwise, all we can suggest is what you already
know, that there may be possible leading characters that are messing things
up.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"mmm206" wrote:

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) file from so I could perform some analysis on the
smaller file. I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. The lookup_value that I am trying
to match is text. I've already sorted then tried CLEANing and TRIMming to no
avail. I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string.
But it seems arbitrary since some lookups are returned correctly and others
are not.

Vlookup of text returning "#Value"
 

You're welcome. Perhaps you can tell us what work around you used to
solve the problem.

Pete

On Apr 6, 8:43*pm, mmm206 wrote:
You are right, the path is correct, I had just taken it out to save space.. *I
found a work around for this problem, so I should be good for now. *Thanks
for your help!



"mmm206" wrote:
sorry, but a little unfamiliar with this forum. *Where can I post to?
Update on the issue. *I also did some additional troubleshooting. I saw that
the smallest string length giving me this issue was 256 char, the longest
string that worked was 254. *I tried to trim the text using LEFT(value, 255),
now the vlookup returns #N/A error. *not sure if this is moving in the right
direction.

"Luke M" wrote:

Please post examples of your data, both the lookup value, and the value you
are trying to match to. Otherwise, all we can suggest is what you already
know, that there may be possible leading characters that are messing things
up.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"mmm206" wrote:

I originally had a rather large file (5K rows X 40 colums) that I created a
new smaller (~5 columns) *file from so I could perform some analysis on the
smaller file. *I am now using vlookup on the smaller file to add back in a
column of data or 2 from the original file. *The vlookup is working on about
4000 of the 5000 values and returning the correct info, except for about 1000
lines where I am getting "#Value" errors. *The lookup_value that I am trying
to match is text. *I've already sorted then tried CLEANing and TRIMming to no
avail. *I have a feeling that this issue has something to do with formatting,
or possibly leading ", - or ( characters, or the length of the text string. *
But it seems arbitrary since some lookups are returned correctly and others
are not.- Hide quoted text -

- Show quoted text -