# Thread: vlookup for partial number

1. Registered User
Join Date
Mar 2004
Posts
361

## Unanswered: vlookup for partial number

I have a 17 digit and letter code that I need to look up by searching by the last 8 numbers.

Ex. WPO2343CA1U456985 is associated with 1100007152 on a worksheet.

I need to find 1100007152 by using 1U456985. Is that possible or does it have to be an exact match?

2. Registered User
Join Date
Jan 2004
Posts
492

## Re: vlookup for partial number

Originally posted by tjarvas
I have a 17 digit and letter code that I need to look up by searching by the last 8 numbers.

Ex. WPO2343CA1U456985 is associated with 1100007152 on a worksheet.

I need to find 1100007152 by using 1U456985. Is that possible or does it have to be an exact match?

You can do one of 2 things:

Select col
from your_table
where col like '*1U456985'

or

if its always going to be the last 8 numbers, try this:

Select col
from your_table
where right(col, 8) = '1U456985'

3. Stuck on my opinions...
Join Date
Nov 2003
Posts
1,487
I believe it is possible but it would be rather difficult to explain here. There are a some questions to be asked about before attempting such a search.

It would be easier if you were to attach a short copy or sample of your database (if possible) so that we can work with and test the task. By doing so, all the questions are answered.

4. Registered User
Join Date
Mar 2004
Posts
361
Actually I just did a mid function and searched that way. The only other problem I have is that sometimes the 16 digit number is associates with 2 of the 10 digit numbers. I don't know how to return all of the matching numbers at that point.

5. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Originally posted by tjarvas
Actually I just did a mid function and searched that way. The only other problem I have is that sometimes the 16 digit number is associates with 2 of the 10 digit numbers. I don't know how to return all of the matching numbers at that point.
Sounds like you have some normalization problems. Do you have authority to add keys to the appropriate tables?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•