vlookup-When using the data in a cell as a reference for a vlookup

When using the data in a cell as a reference for a vlookup how do I only
consider the first three characters in that cell.

ie,
=VLOOKUP(A1,A1:B10,2,FALSE)
but I only want to consider the first three characters in A1

Thanks,
Rick

=vlookup(left(a1,3),a1:b10,2,false)

Although, your look up range (a1:b10) looks kind of strange to me.)

Didn't you get a circular reference error when you tried this?
(maybe A2:B10 or even sheet2!a1:b10???)



R D S wrote:
>
> When using the data in a cell as a reference for a vlookup how do I only
> consider the first three characters in that cell.
>
> ie,
> =VLOOKUP(A1,A1:B10,2,FALSE)
> but I only want to consider the first three characters in A1
>
> Thanks,
> Rick

--

Dave Peterson

Dave Peterson wrote:
> =vlookup(left(a1,3),a1:b10,2,false)
>
> Although, your look up range (a1:b10) looks kind of strange to me.)
>
> Didn't you get a circular reference error when you tried this?
> (maybe A2:B10 or even sheet2!a1:b10???)
>
>
>
> R D S wrote:
>
>>When using the data in a cell as a reference for a vlookup how do I only
>>consider the first three characters in that cell.
>>
>>ie,
>>=VLOOKUP(A1,A1:B10,2,FALSE)
>>but I only want to consider the first three characters in A1
>>
>>Thanks,
>>Rick
>
>

Dave Peterson's comment is puzzling.

=VLOOKUP(LEFT(A1,3)&"*",A1:B10,2,FALSE)

Alan Beban

'Cause I put it in A1!!!!

(watta dope!)



Alan Beban wrote:

>
> Dave Peterson's comment is puzzling.
>
> =VLOOKUP(LEFT(A1,3)&"*",A1:B10,2,FALSE)
>
> Alan Beban

--

Dave Peterson

Yes sorry I relayed it incorrectly, the lookup goes between two sheets.
Thanks for the fix though.

Rick

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =vlookup(left(a1,3),a1:b10,2,false)
>
> Although, your look up range (a1:b10) looks kind of strange to me.)
>
> Didn't you get a circular reference error when you tried this?
> (maybe A2:B10 or even sheet2!a1:b10???)
>
>
>
> R D S wrote:
> >
> > When using the data in a cell as a reference for a vlookup how do I only
> > consider the first three characters in that cell.
> >
> > ie,
> > =VLOOKUP(A1,A1:B10,2,FALSE)
> > but I only want to consider the first three characters in A1
> >
> > Thanks,
> > Rick
>
> --
>
> Dave Peterson

Huh? And I thought Dave Peterson's comment was puzzling!

Alan Beban

R D S wrote:
> Yes sorry I relayed it incorrectly, the lookup goes between two sheets.
> Thanks for the fix though.
>
> Rick
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>
>>=vlookup(left(a1,3),a1:b10,2,false)
>>
>>Although, your look up range (a1:b10) looks kind of strange to me.)
>>
>>Didn't you get a circular reference error when you tried this?
>>(maybe A2:B10 or even sheet2!a1:b10???)
>>
>>
>>
>>R D S wrote:
>>
>>>When using the data in a cell as a reference for a vlookup how do I only
>>>consider the first three characters in that cell.
>>>
>>>ie,
>>>=VLOOKUP(A1,A1:B10,2,FALSE)
>>>but I only want to consider the first three characters in A1
>>>
>>>Thanks,
>>>Rick
>>
>>--
>>
>>Dave Peterson
>
>
>

My puzzling comment was caused by a stupid error on my part (I put the formula
in A1).

But I read the OP's original question to ignore everything after the first three
characters to mean that the table was laid out like:

abc 1
bcd 2
cde 3
def 4

And the value in A1 looked like: abc-1234-asdf.

==
On the other hand, maybe the OP tried both responses, liked yours better and
just said thanks in one message.

Alan Beban wrote:
>
> Huh? And I thought Dave Peterson's comment was puzzling!
>
> Alan Beban
>
> R D S wrote:
> > Yes sorry I relayed it incorrectly, the lookup goes between two sheets.
> > Thanks for the fix though.
> >
> > Rick
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>=vlookup(left(a1,3),a1:b10,2,false)
> >>
> >>Although, your look up range (a1:b10) looks kind of strange to me.)
> >>
> >>Didn't you get a circular reference error when you tried this?
> >>(maybe A2:B10 or even sheet2!a1:b10???)
> >>
> >>
> >>
> >>R D S wrote:
> >>
> >>>When using the data in a cell as a reference for a vlookup how do I only
> >>>consider the first three characters in that cell.
> >>>
> >>>ie,
> >>>=VLOOKUP(A1,A1:B10,2,FALSE)
> >>>but I only want to consider the first three characters in A1
> >>>
> >>>Thanks,
> >>>Rick
> >>
> >>--
> >>
> >>Dave Peterson
> >
> >
> >

--

Dave Peterson

My thought was that the table was laid out

abc1 1
abc2 2
xyz27 3
abc3 4

But on reflection *that* now seems to me kind of silly . . . I think.

Oh well!

Alan Beban

Dave Peterson wrote:
> My puzzling comment was caused by a stupid error on my part (I put the formula
> in A1).
>
> But I read the OP's original question to ignore everything after the first three
> characters to mean that the table was laid out like:
>
> abc 1
> bcd 2
> cde 3
> def 4
>
> And the value in A1 looked like: abc-1234-asdf.
>
> ==
> On the other hand, maybe the OP tried both responses, liked yours better and
> just said thanks in one message.
>
> Alan Beban wrote:
>
>>Huh? And I thought Dave Peterson's comment was puzzling!
>>
>>Alan Beban
>>
>>R D S wrote:
>>
>>>Yes sorry I relayed it incorrectly, the lookup goes between two sheets.
>>>Thanks for the fix though.
>>>
>>>Rick
>>>
>>>"Dave Peterson" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>
>>>>=vlookup(left(a1,3),a1:b10,2,false)
>>>>
>>>>Although, your look up range (a1:b10) looks kind of strange to me.)
>>>>
>>>>Didn't you get a circular reference error when you tried this?
>>>>(maybe A2:B10 or even sheet2!a1:b10???)
>>>>
>>>>
>>>>
>>>>R D S wrote:
>>>>
>>>>
>>>>>When using the data in a cell as a reference for a vlookup how do I only
>>>>>consider the first three characters in that cell.
>>>>>
>>>>>ie,
>>>>>=VLOOKUP(A1,A1:B10,2,FALSE)
>>>>>but I only want to consider the first three characters in A1
>>>>>
>>>>>Thanks,
>>>>>Rick
>>>>
>>>>--
>>>>
>>>>Dave Peterson
>>>
>>>
>>>
>