VLOOKUP to match a name in different format

[rossp1977]

Hi,

I have a spreadsheet with staff names in format FORENAME SURNAME, and I want to extract data from a different report where the layout of the staff name is SURNAME, FORENAME.

Any suggestion how to perform a VLOOKUP function to pull the info I require without needing to change the layout of either spreadsheet?

Thanks

[ChemistB]

Give us some worse case examples, Do you have middle initials, double surnames (mary jones smith), double forenames, xxx the third? How do they appear in both tables? I assume in both cases, we are talking about a single cell containing both surname and forename?

[rossp1977]

Thanks for reply - just solved anyway as some of the staff have same forenames, but none of them have same surnames, so used this: -

VLOOKUP((CONCATENATE("*",(RIGHT($A4,4)),"*"))

Partially matching the last 4 characters of their surname

Will work until we get staff with same surnames, but workable for time being at least!