VLookup help, or similar function help, Please!

[kepleycm]

Is it possible to search for multiple items in one cell separated by a comma? In cell C3 I have abbreviations such as JS, JR, JK and many more. I alsohave a table on Page2 with abbreviations and emails. I wish to search the items in C3 referencing the abbreviation table, and return their corresponding emails separated by a ";". I wish for it to look something like.... In cell C3 have the abbreviations JS, JR, JK and then D3 will automatically return their emails (e-mail address removed); (e-mail address removed); (e-mail address removed). Thanks for all your help, and time.

=VLOOKUP(TRIM(LEFT(SUBSTITUTE(D3,",",REPT(" ",20)),20)),$A$2:$B$7,2,FALSE)&","&VLOOKUP(TRIM(MID(SUBSTITUTE(D3,",",REPT(" ",20)),20,20)),$A$2:$B$7,2,FALSE)&","&VLOOKUP(TRIM(RIGHT(SUBSTITUTE(D3,",",REPT(" ",20)),20)),$A$2:$B$7,2,FALSE)

^ above is the code I have so far, but it will only let me do three entries, and I will have much more.

 

[Claus Busch]

Hi,

Am Thu, 27 Feb 2014 10:31:04 -0800 (PST) schrieb (e-mail address removed):

Is it possible to search for multiple items in one cell separated by a comma? In cell C3 I have abbreviations such as JS, JR, JK and many more. I also have a table on Page2 with abbreviations and emails. I wish to search the items in C3 referencing the abbreviation table, and return their corresponding emails separated by a ";". I wish for it to look something like.... In cell C3 have the abbreviations JS, JR, JK and then D3 will automatically return their emails (e-mail address removed); (e-mail address removed); (e-mail address removed). Thanks for all your help, and time.

put following code in a standard module and call the function on the
sheet with:
=MailAdr(C3)

Function MailAdr(myRng As Range) As String
Dim arrIn As Variant
Dim i As Long
Dim LRow As Long
Dim c As Range

arrIn = Split(myRng, ", ")
For i = LBound(arrIn) To UBound(arrIn)
With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set c = .Range("A1:A" & LRow).Find(arrIn(i), LookIn:=xlValues)
If Not c Is Nothing Then
MailAdr = MailAdr & c.Offset(, 1) & ", "
End If
End With
Next
MailAdr = Left(MailAdr, Len(MailAdr) - 2)
End Function


Regards
Claus B.

 

[kepleycm]

Hi,



Am Thu, 27 Feb 2014 10:31:04 -0800 (PST) schrieb (e-mail address removed):






put following code in a standard module and call the function on the

sheet with:

=MailAdr(C3)



Function MailAdr(myRng As Range) As String

Dim arrIn As Variant

Dim i As Long

Dim LRow As Long

Dim c As Range



arrIn = Split(myRng, ", ")

For i = LBound(arrIn) To UBound(arrIn)

With Sheets("Sheet2")

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

Set c = .Range("A1:A" & LRow).Find(arrIn(i), LookIn:=xlValues)

If Not c Is Nothing Then

MailAdr = MailAdr & c.Offset(, 1) & ", "

End If

End With

Next

MailAdr = Left(MailAdr, Len(MailAdr) - 2)

End Function





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thank you So much, it works perfectly.