Lookup multiple lines

F

Freeflyer

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.
 
D

Domenic

Assumptions:

A2:B11 contains List 1

D2:D3 contains List 2

Formulas:

F2:

=SUMPRODUCT(COUNTIF(A2:A11,D2:D3))

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!
 
F

Freeflyer

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

Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top