Need vlookup to accept errors in names and name formats.

[Mikey7346]

Hi
I receive a list of several hundred personnel and occupations from various agencies. When I try to input the data onto a master list using a simple vlook up formula I keep getting #NA due to names being spelt incorrectly and different name spacing formats. Could someone please advise if there is a way of sorting out my problem so that I don’t have to correct every other name on the recieved list so that it will enter into my master list. I have attached a sample.
Thanks for any assistance
Mikey

[dilipandey]

Hi Mikey,

Few suggestions:-
1) You can trim the names in the imported list
2) When you apply vlookup, there will be some #n/A, which you can try matching with first name only or n characters from left, if you wish
But still little chance of error would always be there and you might need to audit the results manually

Any chance you have the IDs as well along with names which then can be used for vlookup and results would be more accurate



See attached.. vlookup_sample.xlsx

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

[johnjohns]

Also this link may be of some help
https://www.excelforum.com/showthread.php?t=841842

[Mikey7346]

Hi DILIpandey,
Many, many thanks. Your formula has resulted in a huge reduction in #NA's. The ID's are allocated by myself so they are not on the imported list, would that be of any help?
Thanks and Best regards
Mikey

[dilipandey]

You are welcome Mikey....

If the IDs are not in the imported list then it would not be possible to apply vlookup on the basis of IDs... thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>