Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2014-09-04
Medium Priority
Last Modified: 2014-09-04
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
example pictureSUBSTITUTE--1-.xlsx
Question by:Mandy_
  • 2
  • 2
LVL 27

Expert Comment

by:Glenn Ray
ID: 40304439
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.


Author Comment

ID: 40304521
Dear Glenn,

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

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40304571
Heh...whoops!  Working on two different problems...

Here's the correct file.

Author Closing Comment

ID: 40304593
Great work. Excellent!

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question