Solved

Vlookup from other worksheet building smtp

Posted on 2014-09-09
2
101 Views
Last Modified: 2014-09-09
Dear experts,

i'm seeking for a solution for my problem :
I've a worksheet (array) called "comp" up to 250 entries  (range A2:D250) in this form:
A         B              C               D
CMV    gal.com   dal.com    gil.com
DAL     dal.com
DEL     DEL.com  DAL.COM
FIL       FIL.COM  CEL.COM  GAL.COM
FUL     FUL.COM
MAL    MAL.COM
CFR     CFR.COM
......

In other worksheet called "PD" i've this formula below building prefix from displayname in B3  and
suffix (domain.com) depends on 3-LetterCode in B17.

for example:
B2= jones, mike
B17=CMV

smtp in B4 (!PD) should be mike.jones@mal.com
smtp in B5 (!PD) should be mike.jones@dal.com
smtp in B6 (!PD) should be mike.jones@gil.com

The formula below is working so far but only from a small range in the same worksheet.
How the formula should change for a bigger range in other worksheet called (comp)?
I've tried with (B17,comp!A2:D200,2,FALSE) but not working.

Thanks in advance!!!


=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3;(LEN(B3))-(SEARCH(",";B3))-1)&"."&LEFT(B3;(SEARCH(",";B3)-1))&"@"&VLOOKUP(B17;A200:D204;2;FALSE));" ";"");"/,";"");SUBSTITUTE(B3;" ";"")&"@"&VLOOKUP(B17;A200:D204;2;FALSE))

=IFERROR(SUBSTITUTE(SUBSTITUTE(LOWER(RIGHT(B3;(LEN(B3))-(SEARCH(",";B3))-1)&"."&LEFT(B3;(SEARCH(",";B3)-1))&"@"&VLOOKUP(B17;A200:D204;2;FALSE));" ";"");"/,";"");SUBSTITUTE(B3;" ";"")&"@"&VLOOKUP(B17;A200:D204;2;FALSE))

Open in new window

0
Comment
Question by:Mandy_
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 40311835
If VLOOKUP( (B17,comp!A2:D200,2,FALSE) is not working, then possibly you don't have an exact match.  Look to see if there is an extra space in the cell that should be a match in comp! sheeet.

Also note that you can/should make the table range absolute ("freeze it") so that you can copy down formula without have to manually re-adjust range.

e.g.

VLOOKUP(B17,comp!$A$2:$D$200,2,FALSE)

The $ "freezes" the column/row in the formula so you can copy down/across with changing that parameter.

If you still can't get it, post a sample workbook showing the problem.
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40312215
Thank you so much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Consolidate xl 2010 worksheets with text 2 19
ADD New Entries 7 15
Move entire row to another workbook based on cell value 20 39
Filling Blank Cells 14 10
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now