Multiple IF conditions to check against an entire column

[teddybrosevelt]

Hey All, long time lurker on this excellent forum because of it being such a great resource over the years when I've needed help, however this time I seem to be stumped. Searching has been difficult just because of info I'm needing so I figured it's time to post!

So I have to do a few simple automations using formulas in a list of names and turn it into email addresses. I've made some good progress however I've hit a small obstacle which could be just my misunderstanding.

So in column "A", I have a list of users First and Last names. I've used a formula to take the names in Column A and formulate them to be "first.last@emailaddress.com" into Column B (=LEFT(A1,SEARCH(" ",A1))&"."&MID(A1,SEARCH(" ",A1)+1,99)&"@emailaddress.com". For the most part, it's working fine however I'm trying to come up with some "if" statements for a select few names in the list that go by an abbreviated first name in their email address. Being that it's only a couple, I don't mind manually doing a statement for each unique name, but for example:

Jane Doe	Jane.Doe@emailaddress.com
Jonathan Doe	Jon.Doe@emailaddress.com
Joseph Doe	Joe.Doe@emailaddress.com

<tbody>
</tbody>

Jane Doe's email address is Jane.Doe@emailaddress.com, so there's no need to change, however there are a select few in the list of names who's full name, for example, could be "Jonathan" or "Joseph" but their email address is "Jon" and "Joe."

I've currently got the formula working great to just take the cell containing the First and Last names and turning it into the format of "first.last@emailaddress.com." What I'm wondering, is if I can create some IF statements, even if I have to just take the resulting info from Column B and formulate it into a new Column C that can look through the info in A or B, whichever, and check against the list using multiple IF statements in 1 cell that would say something like "In Column A, IF "Jonathan Doe" then "Jon Doe" or IF "Joseph Doe" then "Joe Doe", if that makes sense. I apologize if there's an official term for what I'm trying to describe. Like I said as well, if it's easier for me to look into doing something with the output in Column B, then that's fine as well. Thank you in advance, any help is very much appreciated!

 

[Eric W]

Welcome from out of the shadows!

You could create a table with alternate names in it like this:

	A	B	C	D	E
1	Name	Email		Alternates
2	Jane Doe	Jane.Doe@emailaddress.com		Jonathan Doe	Jon Doe
3	Jonathan Doe	Jon.Doe@emailaddress.com		Joseph Doe	Joe Doe
4	Joseph Doe	Joe.Doe@emailaddress.com
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B2 =SUBSTITUTE(IFERROR(VLOOKUP(A2,$D$2:$E$10,2,FALSE),A2)," ",".")&"@emailaddress.com" <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the formula in B2 and copy down. It first looks up the name from column A in the Alternates table: if it finds it, it uses the value from column E, otherwise the value from column A.

Hope this helps!

 

[teddybrosevelt]

Welcome from out of the shadows!

You could create a table with alternate names in it like this:

	A	B	C	D	E
1	Name	Email		Alternates
2	Jane Doe	Jane.Doe@emailaddress.com		Jonathan Doe	Jon Doe
3	Jonathan Doe	Jon.Doe@emailaddress.com		Joseph Doe	Joe Doe
4	Joseph Doe	Joe.Doe@emailaddress.com
5

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B2 =SUBSTITUTE(IFERROR(VLOOKUP(A2,$D$2:$E$10,2,FALSE),A2)," ",".")&"@emailaddress.com" <tbody> </tbody>

<tbody>
</tbody>

Put the formula in B2 and copy down. It first looks up the name from column A in the Alternates table: if it finds it, it uses the value from column E, otherwise the value from column A.

Hope this helps!

Thank you!! This definitely does help. If I use another sheet within the excel file to hold just the "extra info" for the formula's that's fine too. If I wanted to call a cell using this formula but from sheet 2 in the file, where would I call sheet 2 in this formula provided? (I mean in the formula code itself ). But yeah, I think this is definitely a step in the direction I need to go so thank you for this!

 

[Eric W]

Glad it helps!

If you want to put the "extra info" on another sheet, you can just reference the other sheet in the formula like this:

=SUBSTITUTE(IFERROR(VLOOKUP(A2,Sheet2!$D$2:$E$10,2,FALSE),A2)," ",".")&"@emailaddress.com"

where the range in red is where the extra info lives.

 

[teddybrosevelt]

Glad it helps!

If you want to put the "extra info" on another sheet, you can just reference the other sheet in the formula like this:

=SUBSTITUTE(IFERROR(VLOOKUP(A2,Sheet2!$D$2:$E$10,2,FALSE),A2)," ",".")&"@emailaddress.com"

where the range in red is where the extra info lives.

Seriously thank you so much! I was able to tweak it a little bit and get exactly what I needed. Can't say I expected anything less from this place. Thanks again, you really saved me from crawling under my desk and crying haha.