I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit
number (last digit is some kind of control digit) and when i use left to
take only the first 9 digits
=VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE)
i get #N/A
if i manuallly delete the 10th digit i get mached data.
is there a problem with using left on vlookup?

=left() returns text. That will never match a real number.

I'd take the integer amount after dividing by 10.

=vlookup(int(f200/10);w198:w201;1;false)

But if you're really only looking to see if there's a match, you could use:

=isnumber(match(int(f200/10);w198:w201,0))

You'll see true or false.

polilop wrote:
>
> I'm using vlookup,i have a 10 digit number which i have to mach with 9 digit
> number (last digit is some kind of control digit) and when i use left to
> take only the first 9 digits
> =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE)
> i get #N/A
> if i manuallly delete the 10th digit i get mached data.
> is there a problem with using left on vlookup?

--

Dave Peterson

you need to convert the text value that LEFT() returns into a numeric value,
try:

=VLOOKUP(VALUE(LEFT(F200;9));W198:W201;1;FALSE)

or

=VLOOKUP(--LEFT(F200;9);W198:W201;1;FALSE)



"polilop" <(E-Mail Removed)> wrote in message
news:%23EXHcAB%(E-Mail Removed)...
> I'm using vlookup,i have a 10 digit number which i have to mach with 9
> digit number (last digit is some kind of control digit) and when i use
> left to
> take only the first 9 digits
> =VLOOKUP( LEFT(F200;9);W198:W201;1;FALSE)
> i get #N/A
> if i manuallly delete the 10th digit i get mached data.
> is there a problem with using left on vlookup?