Excel - Vlookup

gh_user
gh_user used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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

Author

Commented:
Thanks for prompt response
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial