Forum Discussion
Email List: Need Help Fixing An Email List With A Formula
I have an email list where the last names do not match the emails. I need a formula that rescrambles the list to match the correct last name. I attached a sample sheet. I need the formula in Column D. So D2 should = C4 (mlevine345@gmail.com).
Thank you for your help in advance!
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.
5 Replies
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.