VLOOKUP problem

[Jan Kronsell]

I use this formula

VLOOKUP(VALUE(LEFT(A1,3),Sheet1!A1:B10=,2,FALSE)

I the first column in the matrix i have fx '345 that is the number as a
textstring. In the lookup cell (A1) i have 34567 and everything works ok.

But if in the matrix I have '002 and in the lookup cell '002222 I get at
N/A. Even if the number in the cells are typed as text, it is treated as
numbers. I can see that VALUE(LEFT(A1,3) returns 2, not 002 as expected. Is
the anyway to solve this?

Jan

 

[Frank Kabel]

Hi
try
VLOOKUP(LEFT(A1,3),Sheet1!A1:B10=,2,FALSE)

 

[Jan Kronsell]

Hi Frank!

I tried your solutiion, but it only worked partly, and it worked partly. I
discovered that issue was, that I was wrong in my fist assumption, that the
numbers always was typed as text.
Only numbers withy preceding zeros was typed as text. The numbers without
preceding zeros was typed as numbers, so my orignial solution worked for
some cells, but not for all, and your solution for the other cells, but not
for the cells mine worked for - and vice versa. .

I have not written a macro that converts everyting into text, so now your
suggestion works on all cells. Thank you.

Jan