Excel Macro vlookup multiple comma separated values and return multiple comma separated values

Hi:
     I have a workbook that checkes a single column and does a Vlookup for a single value in one cell. I need to do macro that will do a lookup of multiple comma separated values in one cell and return the vlookups for each value within the comma separation into another single cell in my excel sheet. Does anyone know how I could do that?  

cell contains
blue, green, orange

I need the Vlookup to look at a list on a separate sheet which has a 2 column table with the colors in one column and the id numbers in the column right next to it
colors	ID number
blue	103875
gree	99003
orange	10783
yellow	413

Open in new window

and return the values
103875,99003,107873

if they mistype the color I would expect to see the N/A in it:

orange,yello

then they should get 10783,#NA
sierra810Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Function SplitLookup(myTxt As String, myTable As Range) As String
arrValues = Split(myTxt, ",")
For Each Item In arrValues
    Mtch = ""
    On Error Resume Next
    Mtch = WorksheetFunction.VLookup(Item, myTable, 2, 0)
    On Error GoTo 0
    If Mtch = "" Then Mtch = "#NA"
    Res = Res & Mtch & ","
Next
Res = Left(Res, Len(Res) - 1)
SplitLookup = Res
End Function


Sub macro()
a = SplitLookup("gree, red", Range("A1:B5"))
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sierra810Author Commented:
Hi thank you for all your help. I tried it and it keeps giving 2 NA values back when I know the data is in the table. I even tried making it a regular sub instead of a function and just hard coding it in using the actual cell value just to get it going but still NA is returned.

Cell value on sheet 1 has
A-Bomb,Abyss

table on sheet2 has

Character List      Character ID
A-Bomb       1016828
A.I.M.       1009144
Abigor       1009145
Abomination      1009146
Absalom       1009147
Absorbing Man      1009148
Abyss      1009149

I did a debug and stepped into the code but no matter what I do it comes back with
A-Bomb,Abyss      #NA,#NA

Sub CharacterListCheck()
Dim CharacterList As String
Dim Item As Variant
Dim arrValues As Variant
Dim Mtch As String
Dim Res As String

CharacterList = Cells(2, 3).Value
arrValues = Split(CharacterList, ",")
For Each Item In arrValues
    Mtch = ""
    On Error Resume Next
    Mtch = WorksheetFunction.VLookup(CharacterList, (Sheet2!Range("A1:B2000")), 2, True)
    On Error GoTo 0
    If Mtch = "" Then Mtch = "#NA"
    Res = Res & Mtch & ","
Next
Res = Left(Res, Len(Res) - 1)
Cells(2, 4).Value = Res

End Sub

Any ideas on what I am doing wrong?

Thank you in advance for all your help!!!
0
sierra810Author Commented:
was looking at my code and had an ah ah! moment, it now works!

Sub CharacterListCheck()
 Dim CharacterList As String
 Dim Item As Variant
 Dim arrValues As Variant
 Dim Mtch As Variant
  Dim Res As String

 CharacterList = Cells(2, 3).Value
 arrValues = Split(CharacterList, ",")
 For Each Item In arrValues
     Mtch = ""
     On Error Resume Next
     Mtch = WorksheetFunction.VLookup(Item, Worksheets("Sheet2").Range("A2:B2000"), 2, True)
     On Error GoTo 0
     If Mtch = "" Then Mtch = "#NA"
     Res = Res & Mtch & ","
 Next
 Res = Left(Res, Len(Res) - 1)
 Cells(2, 4).Value = Res

End Sub
Thank you!!!!
0
sierra810Author Commented:
thank you for taking the time to help!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.