• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 56
  • Last Modified:

Excel - Vlookup

Ive set up a lookup table for assets but a discrepancy with asset numbering format is creating problems.
The lookup table has data in which the asset number has a letter followed by 3 digits.  eg A001
But the lookup value is A1.  I want to be able to recognise A001 as the same asset number.
How do I do this?
Is it best to convert one set of the asset numbers into the same format as the other?
If so, how do I do this? say A001 to A1
0
gh_user
Asked:
gh_user
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Yes it is best to have the same format at both the places to avoid the problems by not making simple vlookup formula complex.

Assuming your asset number is in A2, then

If asset number always contain 3 digits, try this.....
=LEFT(A2,1)&RIGHT(A2,3)+0

Open in new window


If asset number may contain more than 3 digits also, try this...
=LEFT(A2,1)&RIGHT(A2,LEN(A2)-1)+0

Open in new window

0
 
gh_userAuthor Commented:
Thanks for prompt response
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now