VLOOKUP Question - Look only at 1st Three Characters

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

Can the VLOOKUP "Lookup Value" look at only the first
three characters of a string. For example...

CA 12.9o Can the Lookup Value be based on the first 3
characters C,A,"Space" ?

Thank you in advance.
 
Hi Vasant,

It must be too close to my cocktail hour cause I cannot make that LEFT(A1,3)
work in VLOOKUP, and I'm sure I have done it before?? My example:

This is column A and B.

aa 123 1
ab 123 2
ac 123 3
ad 123 4


In C1 I have a drop down list of the 4 items in column A.
In F1 I have this formula:

=VLOOKUP(LEFT(C1,3),A1:B4,2,0)

Returns #N/A. I tried INDIRECT(C1) but no help either.

I thought I was semi foxy with VLOOKUP but this dog won't hunt for me.
Okay, make me feel more stupid than already feel.

Thanks,
Howard
 
Hi Howard:

I'm a bit confused ... in my newsreader your example lines up as 3 columns,
not 2. What exactly is in each column?

Regards,

Vasant.
 
Hi Vasant,

It is actually 2 columns in my example, I did notice it looked a bit wide
when I pasted it in.
But first column is A and the second is B although it looks like there is a
column in between.

Howard
 
Hi Howard:

Maybe it's too close to my cocktail hour as well!

Is the first column "aa 123" and the second column "1", or is the fist
column "aa" and the second "123 1"? I'm assuming the former

Also, if the possible entries in C1 are identical to column A ("aa 123,
etc.), why do you need to use only the first 3 letters for the lookup value?

Sorry for being so dense!

Regards,

Vasant.
 
The first column is "aa 123" and the second is 1. And the C1 dropdown
entries ARE identical to the column A entries. The exercise is to use the
Left(A1,3) in the Vlookup formula, which I am unable to do. And as you say,
if identical just use the whole value, but for the exercise I wanted to test
the Left(A1,3) in the formula.

Howard
 
Hi Howard:

Aha! Now I understand.

This will not work because the lookup value needs to match an element in the
lookup array exactly. In fact, I can't think of any good practical
applications to do the VLOOKUP(LEFT(A1,3),... business. I thought (perhaps
mistakenly) that the question being asked was: If I have ABC-12345 in A1 and
I want to look up ABC in an array, can I use:

VLOOKUP(LEFT(A1,3),...

as opposed to:

VLOOKUP("ABC",...

which I answered in the affirmative. Sorry for the confusion.

Regards,

Vasant.
 
Hi Vasant,

Okay, I've got it now. Hey, thanks for taking the time to straighten me
out, I appreciate it.

Regards,
Howard
 
Back
Top