Question about vlookup with =left

Luapo

Board Regular
Joined
Sep 12, 2005
Messages
69
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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))
 

Luapo

Board Regular
Joined
Sep 12, 2005
Messages
69
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
I think the references in Andrew's formula to column C should be to column E?
 

Luapo

Board Regular
Joined
Sep 12, 2005
Messages
69

ADVERTISEMENT

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

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
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.
 

Luapo

Board Regular
Joined
Sep 12, 2005
Messages
69

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
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))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,496
Office Version
  1. 365
Platform
  1. Windows
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
 

Luapo

Board Regular
Joined
Sep 12, 2005
Messages
69
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,976
Members
416,953
Latest member
broexc

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

Which adblocker are you using?

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
Top