Question about vlookup with =left

[Luapo]

Here is some background information first. My table is layed out like this (these arent the real values btw just some that i have just made up).

(Shorts No. is placed in cell C4, just for referance)

Short No. Colour No.
E123 Pink E12345678
E123 Pink E12348900
E234 Blue E23456789

This is what it looks like, but there is a formula in Short no. and Colour

in Short No. you have (starting from the first entry - E12345678)

=LEFT(E5,4)

in colour you have

=VLOOKUP(C5,Sheet2!B$4:C$10001,2,FALSE)

the range it looks up is on sheet 2 and look like this (starts in B4)

E123 Pink
E234 Blue

Now my problem is that if the number in No. starts with a number

e.g.

Short No. Colour No.
1234 #N/A 123456789

and the lookup on sheet 2 - 1234 Pink

I get a #N/A as you can see. Now if i enter the short number in manually without the =left formula, it works fine. So it seems like it is something to do with =left.

But as I am new to formulas i can't determine what is going wrong. If someone could point me in the right direction that would be great.

I hope this is coherent also as it still is the morning!

 

[Peter_SSs]

Now if you could bare to stand it, could you explain to me step by step how it works?

As Andrew explained, LEFT returns a string (that is, text) so if VLOOKUP looks for the text '1234' on the other sheet, it does not find it because the other sheet has the number '1234'.

I think Andrew thought, as I did, that if there was no letter at the front of the column E entry, then the column E entry must be a number and so suggested his formula. However, I think there must be text somewhere in all (or some) of the column E numbers that start with four digits.

Now to try and explain the formula:
=IF(ISNUMBER(VALUE(LEFT(E40,4))),LEFT(E40,4)+0,LEFT(E40,4))

ISNUMBER(VALUE(LEFT(E40,4)))
VALUE(LEFT(E40)) take the left 4 characters (text string) and try to interpret them as a number. If this can be done, ISNUMBER will return 'True'. If it cannot be done then VALUE returns an error and ISNUMBER returns 'False'.

So if the left part is a number LEFT(E40)+0 takes the four text characters and by adding 0 forces Excel to convert the four character text string into a number

And if the left part is not a number then just take the four left characters and leave them as a text string.

It is a bit long-winded and difficult to explain in words but I hope this has helped a bit.