Macro to look up and match names?

[FJ]

I have a spreadsheet with a column of names and email addresses. I have to
replace these with a last name and first initial that can be found on another
worksheet. The problem is that each cell contains more than one name and/or
email address, and they all have to stay that way. So, for instance, if I
have all of the following in cell A1:

John Smith, Jane Doe, F. Smith, (e-mail address removed)

I want to get the following result also still in one cell:

Smith, J, Doe, J, Smith, F, Green, A

The other problem is the way that the data is set up on the other worksheet.
It is set up like this:

John Smith Smith, J (e-mail address removed)
J. Smith
Andrew Green Green, A (e-mail address removed) A. Green
Fred Smith Smith, F (e-mail address removed)
F. Smith
Jane Doe Doe, J (e-mail address removed)
J. Doe

The match might not be found in the same column every time, so I don’t think
a function like VLOOKUP would work. I’m not sure that any of the regular
functions would work. I think it probably needs a macro.

Is what I’m trying to do even possible?

Thanks in advance for any information.

 

[FJ]

This is just to clarify the data set up in my original post. For some
reason, it came through all jumbled up so that you can't tell what
information is in what column. Here is the data again without the email
addresses. I think that might be what threw it off. Hopefully this will
post correctly.

John Smith Smith, J J. Smith
Andrew Green Green, A A. Green
Fred Smith Smith, F F. Smith
Jane Doe Doe, J J. Doe

If it doesn't, I guess I will just try to rephrase my question in the
following way: Can VLOOKUP, INDEX, or MATCH be used to look up or match
individual names when there is more than one name in a cell?

Thanks in advance for any information anyone can provide and sorry for the
confusing data.

 

[ryguy7272]

Try Vlookup with a wildcard:
=vlookup("*"&"aaa",
=vlookup("&"*"aaa",


Regards,
Ryan--