Multiple values vlookup? Many to many - Two lists

[itrisk]

Hello,

(I did search for a solution on here but couldn't find, hence the new thread)

I have the following issue if anybody can help:

(Many to many relationships)

List 1

Ref	Property 1	Property 2
0.1	A	E
0.2	B	A
0.3	C	D
0.4	D	B

<tbody>
</tbody>

List 2

Properties	Attributes
A	Blue
A	Green
A	Yellow
B	Red
B	Blue
C	Orange
C	Green
C	Blue
D	Red
E	Purple
E	Green
E	Blue
E	Yellow
E	Orange

<tbody>
</tbody>

Desired output - only an example for one row in List 1:

Ref:	Property 1 (Attributes)	Property 2 (Attributes)
0.1	Blue	Purple
0.1	Green	Green
0.1	Yellow	Blue
0.1	Blue	Yellow
0.1	Green	Orange
0.1	Yellow	Purple
0.1	Blue	Green
0.1	Green	Blue
0.1	Yellow	Yellow
0.1	Blue	Orange
0.1	Green	Purple
0.1	Yellow	Green
0.1	Blue	Blue
0.1	Green	Yellow
0.1	Yellow	Orange

<tbody>
</tbody>

Any thoughts?

(Formula preferred, but macro will do - beggars can't be...)

 

[DanteAmor]

How about:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:31.37px;" /><col style="width:82.69px;" /><col style="width:88.4px;" /><col style="width:18.06px;" /><col style="width:87.45px;" /><col style="width:76.04px;" /><col style="width:18.06px;" /><col style="width:31.37px;" /><col style="width:118.81px;" /><col style="width:115.96px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Ref</td><td >Property 1</td><td >Property 2</td><td > </td><td >Properties</td><td >Attributes</td><td > </td><td >Ref:</td><td >Property 1 (Att)</td><td >Property 2 (Att)</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">0.1</td><td >A</td><td >E</td><td > </td><td >A</td><td >Blue</td><td > </td><td style="text-align:right; ">0.1</td><td >Blue</td><td >Purple</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">0.2</td><td >B</td><td >A</td><td > </td><td >A</td><td >Green</td><td > </td><td style="text-align:right; ">0.1</td><td >Green</td><td >Green</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">0.3</td><td >C</td><td >D</td><td > </td><td >A</td><td >Yellow</td><td > </td><td style="text-align:right; ">0.1</td><td >Yellow</td><td >Blue</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">0.4</td><td >D</td><td >B</td><td > </td><td >B</td><td >Red</td><td > </td><td style="text-align:right; ">0.1</td><td >Blue</td><td >Yellow</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td >B</td><td >Blue</td><td > </td><td style="text-align:right; ">0.1</td><td >Green</td><td >Orange</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td >C</td><td >Orange</td><td > </td><td style="text-align:right; ">0.1</td><td >Yellow</td><td >Purple</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td >C</td><td >Green</td><td > </td><td style="text-align:right; ">0.1</td><td >Blue</td><td >Green</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td >C</td><td >Blue</td><td > </td><td style="text-align:right; ">0.1</td><td >Green</td><td >Blue</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td >D</td><td >Red</td><td > </td><td style="text-align:right; ">0.1</td><td >Yellow</td><td >Yellow</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td >E</td><td >Purple</td><td > </td><td style="text-align:right; ">0.1</td><td >Blue</td><td >Orange</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td >E</td><td >Green</td><td > </td><td style="text-align:right; ">0.1</td><td >Green</td><td >Purple</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td >E</td><td >Blue</td><td > </td><td style="text-align:right; ">0.1</td><td >Yellow</td><td >Green</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td >E</td><td >Yellow</td><td > </td><td style="text-align:right; ">0.1</td><td >Blue</td><td >Blue</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td >E</td><td >Orange</td><td > </td><td style="text-align:right; ">0.1</td><td >Green</td><td >Yellow</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.1</td><td >Yellow</td><td >Orange</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.2</td><td >Red</td><td >Blue</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.2</td><td >Blue</td><td >Green</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.2</td><td >Red</td><td >Yellow</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.2</td><td >Blue</td><td >Blue</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.2</td><td >Red</td><td >Green</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.2</td><td >Blue</td><td >Yellow</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.3</td><td >Orange</td><td >Red</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.3</td><td >Green</td><td >Red</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.3</td><td >Blue</td><td >Red</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.4</td><td >Red</td><td >Red</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">0.4</td><td >Red</td><td >Blue</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=A2</td></tr><tr><td >I2</td><td >{=INDEX($F$2:$F$15,SMALL(IF(ISNUMBER(SEARCH(VLOOKUP(H2,$A$2:$B$5,2,0),$E$2:$E$15)),ROW($F$2:$F$15)),MOD(COUNTIF($H$2:H2,H2)-1,COUNTIF($E$2:$E$15,VLOOKUP(H2,$A$2:$B$5,2,0)))+1)-1)}</td></tr><tr><td >J2</td><td >{=INDEX($F$2:$F$15,SMALL(IF(ISNUMBER(SEARCH(VLOOKUP(H2,$A$2:$C$5,3,0),$E$2:$E$15)),ROW($F$2:$F$15)),MOD(COUNTIF($H$2:H2,H2)-1,COUNTIF($E$2:$E$15,VLOOKUP(H2,$A$2:$C$5,3,0)))+1)-1)}</td></tr><tr><td >H3</td><td >=IF(COUNTIF($H$2:H2,H2)<COUNTIF($E$2:$E$15,VLOOKUP(H2,$A$2:$B$5,2,0))*COUNTIF($E$2:$E$15,VLOOKUP(H2,$A$2:$C$5,3,0)),H2)</td></tr></table></td></tr></table>


Instructions:
1. Put the formula in H2.
2. Put the formula in H3 and drag down.
3. Put the array formulain I2, to accept press Shift + Control + Enter
4. Put the array formula in J2, to accept press Shift + Control + Enter.
5. Copy down the formulas of I2 and J2.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.