I have three columns in excel: FirstName, LastName, and a third column that contains first name, last name and maybe other characters.
Now what I want to do is search in the third column, and if both FirstName and LastName exist in a certain cell (since names in the third column is not in the same order as the first and second column) in the third column, return 1. Otherwise, return 0. The order of FirstName and LastName in the third column should not affect the result.
E.g. Jennifer, Smith, Smith Jennifer > this will return 1, regardless of the first/last name order
Anyone knows how to accomplish this with a formula?
Many Thanks,
To make my question more clear, here is an example.
First Name, Last Name, Name List
Jennifer , Smith , Smith Jennifer
Richard, Borland, Richard Borland (acc) <-this cell has other characters
Mike , Leanne, Tom Jackson
Tom, Jackson , Leanne Mike <-The third columns in the last two rows do not match the order of the first two columns