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!
(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!