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
>>>
>>>
>>>
>

Actually, I was thinking that your solution was better:

abc1234 1
bcd1353 2
cde5135 3

And the input field could be: abc3532 (but only the first 3 were important).

(And sorry about confusing you with my "circular reference" reference <bg>.
After I read your message, you may have heard that forehead slap!)


Alan Beban wrote:
>
> 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
> >>>
> >>>
> >>>
> >

--

Dave Peterson

The table is something like

112a 1
112b 1
112c 1
113a 2
113b 2
114a 3
114b 3
etc........
Where the 1st three numbers are a shape and the letter is a colour.
Since the shape is all that matters in this instance I would for obvious
reasons prefer a list like
112 1
113 2
114 3
Hope that clears things up!
Thanks for the help as ever,
Rick

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Actually, I was thinking that your solution was better:
>
> abc1234 1
> bcd1353 2
> cde5135 3
>
> And the input field could be: abc3532 (but only the first 3 were
important).
>
> (And sorry about confusing you with my "circular reference" reference
<bg>.
> After I read your message, you may have heard that forehead slap!)
>
>
> Alan Beban wrote:
> >
> > 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
> > >>>
> > >>>
> > >>>
> > >
>
> --
>
> Dave Peterson

Thanks for posting back.

R D S wrote:
>
> The table is something like
>
> 112a 1
> 112b 1
> 112c 1
> 113a 2
> 113b 2
> 114a 3
> 114b 3
> etc........
> Where the 1st three numbers are a shape and the letter is a colour.
> Since the shape is all that matters in this instance I would for obvious
> reasons prefer a list like
> 112 1
> 113 2
> 114 3
> Hope that clears things up!
> Thanks for the help as ever,
> Rick
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Actually, I was thinking that your solution was better:
> >
> > abc1234 1
> > bcd1353 2
> > cde5135 3
> >
> > And the input field could be: abc3532 (but only the first 3 were
> important).
> >
> > (And sorry about confusing you with my "circular reference" reference
> <bg>.
> > After I read your message, you may have heard that forehead slap!)
> >
> >
> > Alan Beban wrote:
> > >
> > > 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
> > > >>>
> > > >>>
> > > >>>
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson