I have data that I copied and pasted from a CSV file. The first column
is alphanumeric. I alter that column by using the RIGHT function to
pull out only the numeric characters to create a number. The other
column contains field that I am intersted in using. I then have
another column that is all numeric characters. I am trying to use
Vlookup to see if the value is contained in the numeric only column and
return the field of interest. Everytime I do this I get the #NA error.
Also, I find it funny when I use the find command to find a particular
number, and it finds it in the alphanumeric value but not the numeric
only value (the one truncated using the right function). I think there
is something wierd with using a function to pull numbers only from an
alphanumeric string and then use the vlookup command. I have copied a
part of the table below. Under desired result here is what I try to
use in the first cell of the desired result column. Any help is
appreciated

=vlookup(d2,b:c,2,0)

Alphanumeric numeric Desired field lookup number Desired result
RMNT257269 257269 DP17403 133361 #N/A
RMNT257269 257269 DP17403 250909 #N/A
RMNT262553 262553 DP21614 251260
RMNT133361 133361 DP22281 251260
RMNT261352 261352 FL00161 251261
RMNT265475 265475 FL00431 251273
RMNT264392 264392 FL00432 256619
RMNT265474 265474 FL00433 257268
RMNT265476 265476 FL00434 257269
RMNT265477 265477 FL00435 257316
RMNT265478 265478 FL00436 257867
RMNT267448 267448 FL00608 257900
RMNT250909 250909 MF16557 257985
RMNT250909 250909 MF16557 257986
RMNT250909 250909 MF16557 257987
RMNT250909 250909 MF16557 260310


--
goeppngr
------------------------------------------------------------------------
goeppngr's Profile: http://www.excelforum.com/member.php...o&userid=30906
View this thread: http://www.excelforum.com/showthread...hreadid=505804

You'll need to format your number column as text since you're looking up
text.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"goeppngr" wrote in
message ...

I have data that I copied and pasted from a CSV file. The first column
is alphanumeric. I alter that column by using the RIGHT function to
pull out only the numeric characters to create a number. The other
column contains field that I am intersted in using. I then have
another column that is all numeric characters. I am trying to use
Vlookup to see if the value is contained in the numeric only column and
return the field of interest. Everytime I do this I get the #NA error.
Also, I find it funny when I use the find command to find a particular
number, and it finds it in the alphanumeric value but not the numeric
only value (the one truncated using the right function). I think there
is something wierd with using a function to pull numbers only from an
alphanumeric string and then use the vlookup command. I have copied a
part of the table below. Under desired result here is what I try to
use in the first cell of the desired result column. Any help is
appreciated

=vlookup(d2,b:c,2,0)

Alphanumeric numeric Desired field lookup number Desired result
RMNT257269 257269 DP17403 133361 #N/A
RMNT257269 257269 DP17403 250909 #N/A
RMNT262553 262553 DP21614 251260
RMNT133361 133361 DP22281 251260
RMNT261352 261352 FL00161 251261
RMNT265475 265475 FL00431 251273
RMNT264392 264392 FL00432 256619
RMNT265474 265474 FL00433 257268
RMNT265476 265476 FL00434 257269
RMNT265477 265477 FL00435 257316
RMNT265478 265478 FL00436 257867
RMNT267448 267448 FL00608 257900
RMNT250909 250909 MF16557 257985
RMNT250909 250909 MF16557 257986
RMNT250909 250909 MF16557 257987
RMNT250909 250909 MF16557 260310


--
goeppngr
------------------------------------------------------------------------
goeppngr's Profile:
http://www.excelforum.com/member.php...o&userid=30906
View this thread: http://www.excelforum.com/showthread...hreadid=505804

Try this in your right formula.

=--RIGHT(A1,6)

HTH
Regards,
Howard

"goeppngr" wrote in
message ...

I have data that I copied and pasted from a CSV file. The first column
is alphanumeric. I alter that column by using the RIGHT function to
pull out only the numeric characters to create a number. The other
column contains field that I am intersted in using. I then have
another column that is all numeric characters. I am trying to use
Vlookup to see if the value is contained in the numeric only column and
return the field of interest. Everytime I do this I get the #NA error.
Also, I find it funny when I use the find command to find a particular
number, and it finds it in the alphanumeric value but not the numeric
only value (the one truncated using the right function). I think there
is something wierd with using a function to pull numbers only from an
alphanumeric string and then use the vlookup command. I have copied a
part of the table below. Under desired result here is what I try to
use in the first cell of the desired result column. Any help is
appreciated

=vlookup(d2,b:c,2,0)

Alphanumeric numeric Desired field lookup number Desired result
RMNT257269 257269 DP17403 133361 #N/A
RMNT257269 257269 DP17403 250909 #N/A
RMNT262553 262553 DP21614 251260
RMNT133361 133361 DP22281 251260
RMNT261352 261352 FL00161 251261
RMNT265475 265475 FL00431 251273
RMNT264392 264392 FL00432 256619
RMNT265474 265474 FL00433 257268
RMNT265476 265476 FL00434 257269
RMNT265477 265477 FL00435 257316
RMNT265478 265478 FL00436 257867
RMNT267448 267448 FL00608 257900
RMNT250909 250909 MF16557 257985
RMNT250909 250909 MF16557 257986
RMNT250909 250909 MF16557 257987
RMNT250909 250909 MF16557 260310


--
goeppngr
------------------------------------------------------------------------
goeppngr's Profile:
http://www.excelforum.com/member.php...o&userid=30906
View this thread: http://www.excelforum.com/showthread...hreadid=505804

Your column B in the example looks to be numeric to me (right aligned),
but column D doesn't, although it is difficult to say exactly with how
the table is misaligned. You could try the following amendment to your
lookup formula:

=vlookup(VALUE(d2),b:c,2,0)

Hope this helps.

Pete