VLOOKUP w/ LEFT Function

Function sequence giving me "N/A": =VLOOKUP(LEFT(C6,5),H:I,2,FALSE)

Basically, I have numbers that each start with a unique sequence. The first 5 numbers of that sequence represent a certain cell carrier.

What I want to do is have the function look up the first 5 characters of a cell and depending on the 5 characters, I want it to return a certain value.

My idea with the vlookup was to have the lookup value be the first 5 digits and then in my table, it would take only those 5 digits and return a value I have specified in the second column.

Any help on my particular function or if you have another function, I would greatly appreciate it.

Thanks,
Alan

Does the value in the table (left most value) equal only the five characters you are searching or are they the entire number? They must match.

Originally Posted by rocky1

Does the value in the table (left most value) equal only the five characters you are searching or are they the entire number? They must match.

Yep and they are also ascending.

I should add that I would simply use the IF function but that only deals with 2 scenarios. In my case, I have 3 scenarios.

Maybe you can attach a small sample of your workbook.

I'm leaving in a few minutes, but I'm sure by the time I get back somebody else will have taken a look or I will get it.

Originally Posted by rocky1

Maybe you can attach a small sample of your workbook.

I'm leaving in a few minutes, but I'm sure by the time I get back somebody else will have taken a look or I will get it.

Thanks for the help...

I've attached a simple example document of what I am dealing with.

As you'll see, the first 5 numbers indicate which cell carrier the whole number belongs to.

Originally Posted by teylyn

The result of your LEFT() is a text value. Your lookup table has numbers. So you have to make sure both are of the same data type before you do the lookup. The easiest would be

=VLOOKUP(--LEFT(A1,5),$H$1:$I$3,2,FALSE)

or

=VLOOKUP(LEFT(A1,5)*1,$H$1:$I$3,2,FALSE)

Oh, I changed the lookup table reference. No need to reference the whole column!!!

hth

Awesome stuff! So, that "*1" you added seems to have fixed it. What exactly is it doing if you don't mind me asking?

Thanks,
Alan

Originally Posted by AlanH

Awesome stuff! So, that "*1" you added seems to have fixed it. What exactly is it doing if you don't mind me asking?

Thanks,
Alan

I figured it out. I guess that just makes it turn itself into a number by completing an equation.

Cool. Thanks again for your help.