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
Who is Participating?
Subodh Tiwari (Neeraj)Connect With a Mentor 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.....

Open in new window

If asset number may contain more than 3 digits also, try this...

Open in new window

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.