Forum Discussion
Email List: Need Help Fixing An Email List With A Formula
Deleted , if use your idea that could be
=IFNA(INDEX($C$2:$C$30,MATCH(1,INDEX(1*ISNUMBER(SEARCH($B2,$C$2:$C$30)),0),0)),"=no such==")
I didn't check blank last names here. Also not exact approximation. If, for example, you have Joe Smith, John Smith and Peter Smith.
Please see in the first sheet attached.
Deleted , the issue is you have no same rule for all emails, For example, for Russell Westbrook email starts from russell45, not from rwestbrook as it is for other people.
As variant you could first sort your columns A and B in ascending order, after that to sort only column C (without expansion) - that could be good enough. See second sheet in attached transformed as above.
Thanks SergeiBaklan. Unfortunately you are correct. There is no unique identifier. The document I sent was a small sample of the 22,000 records I have in the main list. Although your solution works with the small sample, it doesn't carry over to the full list due to missing records, duplicate last names, strange email strings, etc.
Essentially I want to be able to search a string column C for a matching last name in column B and return the result in column D. If there's no match (such as a first name as the email or a missing record it should return an error, or blank). I was thinking some kind of index partial match such as =INDEX(C2:C6,MATCH("*"&B2&"*",B2:B6,0)), but this didn't work.
Deleted , if use your idea that could be
=IFNA(INDEX($C$2:$C$30,MATCH(1,INDEX(1*ISNUMBER(SEARCH($B2,$C$2:$C$30)),0),0)),"=no such==")
I didn't check blank last names here. Also not exact approximation. If, for example, you have Joe Smith, John Smith and Peter Smith.
Please see in the first sheet attached.
Thanks so muchSergeiBaklan. This formula works and is the best solution to my problem. Unfortunately I have way more duplicate last names than I thought. Also some last names like "Hager" fit within an email address containing "Hagerty" and other short last names are giving me problems.
It's not a perfect solution but it helps for now. Thank you very much.