asked on

Excel - anybody could help me to complete this competition with vlookup

Dear EE,

i've this time this formula below and is working great so far.

=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3;(LEN(B3))-(SEARCH(",";B3))-1)&"."&LEFT(B3;(SEARCH(",";B3)-1))&"@"&VLOOKUP(B24;A18:B21GUL;2;FALSCH));" ";"");"/,";"");SUBSTITUTE(B3;" ";"")&"@"&VLOOKUP(B24;A18:B21;2;FALSE))

but i like 3 different smtp's in D9,D10,D11 depends on Cell B24

if B24 = GUL D9 = gul.com, D10 = gul.com D11=gul.com
if B24 = GUL2 D9 = gul.com, D10 = gul.com D11=gul.com
if B24 = GIL D9 = gul.com, D10 = gil.com D11=gul.com
if B24 = GIL2 D9 = gul.com, D10 = gil.com D11=gul.com
if B24 = LEL D9 = lel.com, D10 = leld.com D11=gel.com

Pls see picture and excel example below

SUBSTITUTE--1-.xlsx

Glenn Ray

The three formulas in D9, D10, and D11 were modified to look at a larger range (A18:D22) and to look in respectively further columns each time:

D9: =IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&VLOOKUP(B24,A18:D22,2,FALSE))," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&VLOOKUP(B24,A18:D22,2,FALSE))

D10: =IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&VLOOKUP(B24,A18:D22,3,FALSE))," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&VLOOKUP(B24,A18:D22,2,FALSE))

D11: =IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3,(LEN(B3))-(SEARCH(",",B3))-1)&"."&LEFT(B3,(SEARCH(",",B3)-1))&"@"&VLOOKUP(B24,A18:D22,4,FALSE))," ",""),"/,",""),SUBSTITUTE(B3," ","")&"@"&VLOOKUP(B24,A18:D22,2,FALSE))

Note that I did not change any of the casing for the name (i.e., upper/lower case).

I also intentionally added errors to the lookup range (=1/0...#DIV/0!) in order to force the function to return the default domain name.

Example workbook attached.

Regards,
-Glenn
EE-Formulas.xls

Mandy_

ASKER

Dear Glenn,

thank you, i will try that but the attachment seems to be a wrong one...

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mandy_

ASKER

Great work. Excellent!