Solved

Vlookup from other worksheet building smtp

Posted on 2014-09-09
2
100 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
Comment Utility
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_
Comment Utility
Thank you so much
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 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

8 Experts available now in Live!

Get 1:1 Help Now