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.