?
Solved

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

Posted on 2014-07-15
5
Medium Priority
?
4,091 Views
Last Modified: 2014-07-17
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
0
Comment
Question by:sierra810
  • 3
4 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40198619
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
 

Author Comment

by:sierra810
ID: 40202456
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
 

Author Comment

by:sierra810
ID: 40203476
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
 

Author Closing Comment

by:sierra810
ID: 40203478
thank you for taking the time to help!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 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