Lookup multiple lines

[Guest]

Hi,

Not sure if this is possible using functions or if it requires a macro.
I have two lists, one containing all possible variations of items and one
containing a list of items. What I need to do is combine these lists to
produce a third list that contains every version of the items in the second
list.

Example:

List 1
Column A Column B
Widget Red
Widget Green
Widget Blue
Gadget Red
Gadget Yellow
Thingy Purple
Thingy Pink
DooDah Orange
Doodah Green
Doodah Turquoise

List 2
Column A
Gadget
Doodah

List 3 - Desired Output
Gadget Red
Gadget Yellow
DooDah Orange
Doodah Green
Doodah Turquoise

Any ideas? Thanks.

 

[Domenic]

Assumptions:

A2:B11 contains List 1

D23 contains List 2

Formulas:

F2:

=SUMPRODUCT(COUNTIF(A2:A11,D23))

G2, copied down and across:

=IF(ROWS(G$2:G2)<=$F$2,INDEX(A$2:A$11,SMALL(IF(ISNUMBER(MATCH($A$2:$A$11,
$D$2:$D$3,0)),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(G$2:G2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

Thanks very much. Just tested it using the example below and it seems perfect.

Michael