software

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)

AlphanumericnumericDesired fieldlookup numberDesired result
RMNT257269257269DP17403133361#N/A
RMNT257269257269DP17403250909#N/A
RMNT262553262553DP21614251260
RMNT133361133361DP22281251260
RMNT261352261352FL00161251261
RMNT265475265475FL00431251273
RMNT264392264392FL00432256619
RMNT265474265474FL00433257268
RMNT265476265476FL00434257269
RMNT265477265477FL00435257316
RMNT265478265478FL00436257867
RMNT267448267448FL00608257900
RMNT250909250909MF16557257985
RMNT250909250909MF16557257986
RMNT250909250909MF16557257987
RMNT250909250909MF16557260310--
goeppngr
------------------------------------------------------------------------
goeppngr's Profile: www.excelforum.com/member.php...oamp;userid=30906
View this thread: www.excelforum.com/showthread...hreadid=505804You'll need to format your number column as text since you're looking up
text.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

quot;goeppngrquot; gt; wrote in
message ...
gt;
gt; I have data that I copied and pasted from a CSV file. The first column
gt; is alphanumeric. I alter that column by using the RIGHT function to
gt; pull out only the numeric characters to create a number. The other
gt; column contains field that I am intersted in using. I then have
gt; another column that is all numeric characters. I am trying to use
gt; Vlookup to see if the value is contained in the numeric only column and
gt; return the field of interest. Everytime I do this I get the #NA error.
gt; Also, I find it funny when I use the find command to find a particular
gt; number, and it finds it in the alphanumeric value but not the numeric
gt; only value (the one truncated using the right function). I think there
gt; is something wierd with using a function to pull numbers only from an
gt; alphanumeric string and then use the vlookup command. I have copied a
gt; part of the table below. Under desired result here is what I try to
gt; use in the first cell of the desired result column. Any help is
gt; appreciated
gt;
gt; =vlookup(d2,b:c,2,0)
gt;
gt; Alphanumeric numeric Desired field lookup number Desired result
gt; RMNT257269 257269 DP17403 133361 #N/A
gt; RMNT257269 257269 DP17403 250909 #N/A
gt; RMNT262553 262553 DP21614 251260
gt; RMNT133361 133361 DP22281 251260
gt; RMNT261352 261352 FL00161 251261
gt; RMNT265475 265475 FL00431 251273
gt; RMNT264392 264392 FL00432 256619
gt; RMNT265474 265474 FL00433 257268
gt; RMNT265476 265476 FL00434 257269
gt; RMNT265477 265477 FL00435 257316
gt; RMNT265478 265478 FL00436 257867
gt; RMNT267448 267448 FL00608 257900
gt; RMNT250909 250909 MF16557 257985
gt; RMNT250909 250909 MF16557 257986
gt; RMNT250909 250909 MF16557 257987
gt; RMNT250909 250909 MF16557 260310
gt;
gt;
gt; --
gt; goeppngr
gt; ------------------------------------------------------------------------
gt; goeppngr's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30906
gt; View this thread: www.excelforum.com/showthread...hreadid=505804
gt;
Try this in your right formula.

=--RIGHT(A1,6)

HTH
Regards,
Howard

quot;goeppngrquot; gt; wrote in
message ...
gt;
gt; I have data that I copied and pasted from a CSV file. The first column
gt; is alphanumeric. I alter that column by using the RIGHT function to
gt; pull out only the numeric characters to create a number. The other
gt; column contains field that I am intersted in using. I then have
gt; another column that is all numeric characters. I am trying to use
gt; Vlookup to see if the value is contained in the numeric only column and
gt; return the field of interest. Everytime I do this I get the #NA error.
gt; Also, I find it funny when I use the find command to find a particular
gt; number, and it finds it in the alphanumeric value but not the numeric
gt; only value (the one truncated using the right function). I think there
gt; is something wierd with using a function to pull numbers only from an
gt; alphanumeric string and then use the vlookup command. I have copied a
gt; part of the table below. Under desired result here is what I try to
gt; use in the first cell of the desired result column. Any help is
gt; appreciated
gt;
gt; =vlookup(d2,b:c,2,0)
gt;
gt; Alphanumeric numeric Desired field lookup number Desired result
gt; RMNT257269 257269 DP17403 133361 #N/A
gt; RMNT257269 257269 DP17403 250909 #N/A
gt; RMNT262553 262553 DP21614 251260
gt; RMNT133361 133361 DP22281 251260
gt; RMNT261352 261352 FL00161 251261
gt; RMNT265475 265475 FL00431 251273
gt; RMNT264392 264392 FL00432 256619
gt; RMNT265474 265474 FL00433 257268
gt; RMNT265476 265476 FL00434 257269
gt; RMNT265477 265477 FL00435 257316
gt; RMNT265478 265478 FL00436 257867
gt; RMNT267448 267448 FL00608 257900
gt; RMNT250909 250909 MF16557 257985
gt; RMNT250909 250909 MF16557 257986
gt; RMNT250909 250909 MF16557 257987
gt; RMNT250909 250909 MF16557 260310
gt;
gt;
gt; --
gt; goeppngr
gt; ------------------------------------------------------------------------
gt; goeppngr's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30906
gt; View this thread: www.excelforum.com/showthread...hreadid=505804
gt;
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