vlookup problems with left

[polilop]

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]

=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.

 

[Steve Dunn]

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)