vlookup

[R D S]

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]

=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???)

 

[Alan Beban]

=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???)

Dave Peterson's comment is puzzling.

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

Alan Beban

 

[Dave Peterson]

'Cause I put it in A1!!!!

(watta dope!)

Dave Peterson's comment is puzzling.

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

Alan Beban

 

[R D S]

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

Rick

 

[Alan Beban]

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

Alan Beban

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

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.

==

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

Alan Beban

 

[Alan Beban]

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]

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

 

[R D S]

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]

Thanks for posting back.

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