Solved

Vlookup from other worksheet building smtp

Posted on 2014-09-09
2
102 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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