I am combining multiple data sources that list employee names differently ("Last, First", "First 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 indicates 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)