VLOOKUP Issue

I have a bit of an issue with vlookups. Ill try and explain this as best as I can.

Within one workbook I have two worksheets - Sheet 1: Full User Name and Sheet 2: User Name.

Sheet 1 contains 161 usernames in the following format - First Name Last Name, eg John Smith.

Sheet 2 contains 394 in various formats - First Name Last Name (John Smith), First Initial Last Name (JSmith) or Last Name First Initial (SmithJ).

I need to confirm which of the names that are in Sheet 1 is also in Sheet 2. Normally a VLOOKUP would have sufficed but because of the different naming conventions its proving to be extremely difficult.

Is there a formula I can use to make all the names in Sheets 1 and 2 the same format without having to go through a manual data cleansing process? If there is, I can just use a VLOOKUP formula from there.

NB - This is the formula I was using until I realised that the naming convention was different across the two sheets - =VLOOKUP(D4,'OBS-London'!$C$5:$C$394,1,1).

Any help is greatly appreciated.

if you can guarantee that names have a maximum of one initial, and always have one initial, and the initial is always first, then yes.

BUT JSmith could be Mr J Smith or Mr H Jsmit

so you need to do it manually.....

Originally Posted by robert111

if you can guarantee that names have a maximum of one initial, and always have one initial, and the initial is always first, then yes.

BUT JSmith could be Mr J Smith or Mr H Jsmit

so you need to do it manually.....

All the names in Sheet 2 are in any of the three formats noted in the initial post.

So its either John Smith, JSmith or SmithJ. There is no 'Mr' prefix or any other kind of prefix however, the initial can be either first or last so I guess it will have to be a manual process.

Thanks for your help.