How do I match the text of two cells to one cell?

I am combining multiple data sources that list employee names differently ("Last, First", "Fist Last", "Last First", etc.) into one workbook and thus one table. I want to avoid spending a lot of time manipulating the imported data.

I want to use a formula that will tell me if someone's name is in the row (A) regardless of how the name is displayed. Below is a formula that I was using, however it does not guarantee that the first and last name are matched in the same cell/row. (yellow highlight are the examples that are incorrect)

Does anyone have any suggestions or ideas?

Formula: =IFERROR(IF(MATCH(""&C3&"",$A$2:$A$6,0)>=0,IF(MATCH(""&D3&"",$A$2:$A$6,0)>=0,1,0)),0)

Recent Questions...