Multiple lookup using Excel VBA

Asked Nov 01 '17 at 13:34

Active Nov 01 '17 at 13:36

Viewed 92 times

I have two tables in Excel 2013 in two different sheets - both starting in Cell A1:

Sheet A table

A1:A3 | B1:B3    
a001  | ?    
a002  | ?    
b001  | ?

Sheet B table

A1:A5 | B1:B5    
b001  | 75    
a002  | 85    
b001  | 60    
b002  | 25    
b001  | 50

I would like to make a simple lookup in the Sheet B table range A1:A5 for each cell value in Sheet A table range A1:A3. Where there is a match I would like the corresponding value in Sheet B table range B1:B5 to be copied to the Sheet A table B1:B3. If there are multiple matches they all should be copied and displayed separated by a semicolon. The result should look like this:

Sheet A table (updated)

A1:A3 | B1:B3    
a001  |    
a002  | 85    
b001  | 75;60;50

I've started with this code but can't get further:

Sub DeleteRow()
For Each c In Range("A1:A5")
    If c = ??? Then ???
Next
End Sub

I hope somebody can help.

edited Jul 09 '18 at 19:34

Community

asked Nov 01 '17 at 13:34

McClaudLive

If you have Office 365 Excel you can use TEXTJOIN(). – Scott Craner Nov 01 '17 at 13:35
If not see here for some code that mimics TEXTJOIN and how to use it: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell – Scott Craner Nov 01 '17 at 13:37
I have come up with a simple solution. Thank you for the input. :) – McClaudLive Nov 03 '17 at 14:56

Multiple lookup using Excel VBA

0 Answers0