VBA code to look up a list of values based on entered values and return all values.

Hi All,

I have a problem which i am hoping someone can help me solve using VBA, I have a table that looks like this:

Group No. (Key) Material
9400028741 600-1014:TELSPEC
9400028741 597/29:TELSPEC
9400030162 BFD328507:ERICSSON
9400030162 BFD328507/11:ERICSSON
9400030162 BMS 404 030/2:EMERSONRGY
9400030162 INV-1000VA RM:CODECOM
11GHZODU GE9234:SIAE
11GHZODU GE9348:SIAE
12647I Q9662A:NEC
12647I Q9662B:NEC
12647I Q9668A:NEC
12647I Q9668B:NEC

What i need is to be able to enter a list of materials (from different groups) and then have an output of all the materials that belong to the group for which the materials i entered to begin with belong to.

So if i entered the following materials in A1-A3:
BFD328507:ERICSSON
Q9668B:NEC
GE9234:SIAE

Then the output would be:
BFD328507:ERICSSON
BFD328507/11:ERICSSON
BMS 404 030/2:EMERSONRGY
INV-1000VA RM:CODECOM
Q9668B:NEC
Q9668A:NEC
Q9662B:NEC
Q9662A:NEC
GE9234:SIAE
GE9348:SIAE

Hopefully this makes sense, thanks in advance!

Hi Kaper,

This worked great! Thanks so much for your help!