Solved

Vlookup from other worksheet building smtp

Posted on 2014-09-09
2
106 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

749 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