VLOOKUP QUERY

[John Moore]

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 ?

 

[John Moore]

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

Col A Col B Col C
6223BD2 6223 #NA

 

[RWS]

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

 

[bpeltzer]

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)

 

[SteveG]

In C try,

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

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


HTH
Stev

 

[LACA]

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

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

 

[John Moore]

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