Trying to get a simple Vlookup to work ,,, and it has me perplexed ,,,,
example below ,,,,,, column A contains the original data, column B contains a
LEFT function of colunm A ,,, i.e. =LEFT(A1,4,0), to return the first 4
characters of A1, column C is where I have a Vlookup, lookup is based on a
range, called data, so would look like this , =VLOOKUP(B1,data,2,0) ,,,,,
this returns an #NA error, even though the data in B1 exits in the range, how
do I solution this ,,,,,is there a way to do a vlookup on cell A1 ( first
four characters only ) without doing an =LEFT in coulmn B ?

Sorry ,,, would help if I posted the example ...

Col A Col B Col C
6223BD2 6223 #NA


"John Moore" wrote:

Trying to get a simple Vlookup to work ,,, and it has me perplexed ,,,,
example below ,,,,,, column A contains the original data, column B contains a
LEFT function of colunm A ,,, i.e. =LEFT(A1,4,0), to return the first 4
characters of A1, column C is where I have a Vlookup, lookup is based on a
range, called data, so would look like this , =VLOOKUP(B1,data,2,0) ,,,,,
this returns an #NA error, even though the data in B1 exits in the range, how
do I solution this ,,,,,is there a way to do a vlookup on cell A1 ( first
four characters only ) without doing an =LEFT in coulmn B ?

Your lookup is fine, the prolem is that the left function returns a text value
Make it

=VALUE(LEFT(A1,4))
This will give you a numeric value rather than text and so it should work

--
RWS


"John Moore" wrote:

Sorry ,,, would help if I posted the example ...

Col A Col B Col C
6223BD2 6223 #NA


"John Moore" wrote:

Trying to get a simple Vlookup to work ,,, and it has me perplexed ,,,,
example below ,,,,,, column A contains the original data, column B contains a
LEFT function of colunm A ,,, i.e. =LEFT(A1,4,0), to return the first 4
characters of A1, column C is where I have a Vlookup, lookup is based on a
range, called data, so would look like this , =VLOOKUP(B1,data,2,0) ,,,,,
this returns an #NA error, even though the data in B1 exits in the range, how
do I solution this ,,,,,is there a way to do a vlookup on cell A1 ( first
four characters only ) without doing an =LEFT in coulmn B ?

I'll take a guess that the table in which you're looking for 6223 is numeric;
the result of the LEFT function is still a string (even though it's a string
of numbers). Convert it to a value and your lookup may work:
=vlookup(value(b1),data,2,0)

"John Moore" wrote:

Sorry ,,, would help if I posted the example ...

Col A Col B Col C
6223BD2 6223 #NA


"John Moore" wrote:

Trying to get a simple Vlookup to work ,,, and it has me perplexed ,,,,
example below ,,,,,, column A contains the original data, column B contains a
LEFT function of colunm A ,,, i.e. =LEFT(A1,4,0), to return the first 4
characters of A1, column C is where I have a Vlookup, lookup is based on a
range, called data, so would look like this , =VLOOKUP(B1,data,2,0) ,,,,,
this returns an #NA error, even though the data in B1 exits in the range, how
do I solution this ,,,,,is there a way to do a vlookup on cell A1 ( first
four characters only ) without doing an =LEFT in coulmn B ?

In C try,

=VLOOKUP(LEFT(A1,4),data,2,FALSE)

Copy down as needed. Make sure your named range "data" includes all
the columns in the table as well or the VLOOKUP will return a REF
error. Also make sure that your "data" has no spaces after the 4
characters you are looking up or it will not find it as a match. To
check, on one of your N/A errors, find that value in the data table and
click just to the right of the last letter and hit delete a bunch of
times. If your VLOOKUP now returns the value, you know that is the
issue.


HTH
Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=554757

You can actually eliminate Column B to save space and just use

=VLOOKUP(VALUE(LEFT(A1,4)),DATA,2,FALSE)


--
LACA
------------------------------------------------------------------------
LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381
View this thread: http://www.excelforum.com/showthread...hreadid=554757

Thanks guys ,,, works well ,,,, I had an idea it was because of the numeric
text ,,,, thanks again

"bpeltzer" wrote:

I'll take a guess that the table in which you're looking for 6223 is numeric;
the result of the LEFT function is still a string (even though it's a string
of numbers). Convert it to a value and your lookup may work:
=vlookup(value(b1),data,2,0)

"John Moore" wrote:

Sorry ,,, would help if I posted the example ...

Col A Col B Col C
6223BD2 6223 #NA


"John Moore" wrote:

Trying to get a simple Vlookup to work ,,, and it has me perplexed ,,,,
example below ,,,,,, column A contains the original data, column B contains a
LEFT function of colunm A ,,, i.e. =LEFT(A1,4,0), to return the first 4
characters of A1, column C is where I have a Vlookup, lookup is based on a
range, called data, so would look like this , =VLOOKUP(B1,data,2,0) ,,,,,
this returns an #NA error, even though the data in B1 exits in the range, how
do I solution this ,,,,,is there a way to do a vlookup on cell A1 ( first
four characters only ) without doing an =LEFT in coulmn B ?