Question about vlookup with =left

Luapo

Board Regular
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!

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
The LEFT function returns a string, so the lookup will fail if the table contains a number. Try:

=IF(ISNUMBER(C5),LEFT(C5,4)+0,LEFT(C5,4))

Thankyou for the reply Andrew. It half works, as in it if the No. has a letter at the begginning it will work, but if it has a number it still won't.

I think the references in Andrew's formula to column C should be to column E?

Yeah it was, i did change it still didn't work

Peter I don't suppose you have any ideas do you?

Luapo said:
Yeah it was, i did change it still didn't work

Peter I don't suppose you have any ideas do you?
Andrew's formula worked fine for me.

There must be something else going on with your sheet but I haven't come up with any likely ideas yet.

Peter may you post your sheet using the html thingy, then I can see what our differences are (im not allowed to download stuff at work, hence I cant use it).

Many Thanks.

Yes I have (an idea). What is the full entry in column E that produces 1234 in column C? Does it have any text in it? If so, try:
=IF(ISNUMBER(VALUE(LEFT(E5,4))),LEFT(E5,4)+0,LEFT(E5,4))

Luapo said:
Peter may you post your sheet using the html thingy, then I can see what our differences are (im not allowed to download stuff at work, hence I cant use it).

Many Thanks.
Sheet1 with the =IF(ISNUMBER(VALUE(LEFT(E5,4))),LEFT(E5,4)+0,LEFT(E5,4)) formula
Mr Excel.xls
CDEF
4Short NoColour No
5E123PinkE12345678
61234Blue123456789E
7E234RedE23456789
Sheet1

Sheet2
Mr Excel.xls
ABCD
4E123Pink
51234Blue
6E234Red
7
Sheet2

I was using - =IF(ISNUMBER(E40),LEFT(E40,4)+0,LEFT(E40,4))

then I changed it to your one - =IF(ISNUMBER(VALUE(LEFT(E40,4))),LEFT(E40,4)+0,LEFT(E40,4))

It now works! So thankyou.

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

Thanks in advance.

Replies
7
Views
264
Replies
9
Views
415
Replies
3
Views
429
Replies
1
Views
187
Replies
19
Views
648

Threads
1,211,685
Messages
6,103,291
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back