vlookup query

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 ?

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

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 ?

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

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

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 ?