Multi lookup

[Dunks411]

[h=1]Excel Magic Trick 986 One Lookup Value, Extract Multiple Items, Display Horizontally (A Closer Look)[/h]
Hey guys

Please see my sheet in this workbook https://drive.google.com/file/d/0BxMRcPUPivBMMG9ZOERjSFgyZkE/view?usp=sharing
Prod_var_masterpricelist and forumula in col d
I am working off this tutorial https://www.youtube.com/watch?v=i_It8xViQsY

I want to lookup the value in col a (yes there are duplicates) and return horizontally all the corresponding values (that match the value in col a) so I have...

FNW001	FNW001-1260x850	FNW001	1260x850
FNW001	FNW001-2500x2430	FNW001	2500x2430
FNW001	FNW001-3200x2430	FNW001	3200x2430
FNW001	FNW001-3600x2430	FNW001	3600x2430
FNW001	FNW001-3600x2700	FNW001	3600x2700

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

And I want

FNW001 1260x850 2500x243 3200x2430 3200x2430 3600x2430 3600x2700 Please help!!

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[oldbrewer]

HEAD1

HEAD2

HEAD3

SIZE

FNW001

FNW001-1260x850

FNW001

1260x850

FNW001

FNW001-2500x2430

FNW001

2500x2430

FNW001

FNW001-3200x2430

FNW001

3200x2430

FNW001

FNW001-3600x2430

FNW001

3600x2430

FNW001

FNW001-3600x2700

FNW001

3600x2700

FNW002

FNW002-5500X2750

FN002

5500X2750

Count of SIZE

HEAD1

SIZE

FNW001

FNW002

Grand Total

1260x850

1

1

2500x2430

1

1

3200x2430

1

1

3600x2430

1

1

3600x2700

1

1

5500X2750

1

1

Grand Total

5

1

6

formula for FNW001 is

=IF(LEFT(OFFSET($B$15,0,1),3)="FNW",OFFSET($B$15,0,1),"")

use an intermediate pivot table to get the unique sizes

it will only find FNW numbers and return blanks for everything else

for each FNW number

formula1

formula2

FNW001

1260x850

2500x2430

formula1

=IF(OFFSET($B$15,MATCH($B35,$C$15:$H$15,0),1)=1,OFFSET($B$15,1,MATCH($B35,$C$15:$H$15,0)-1),"")

FNW002

formula2

=IF(OFFSET($B$15,MATCH($B35,$C$15:$H$15,0),1)=1,OFFSET($B$15,2,MATCH($B35,$C$15:$H$15,0)-1),"")

you should be able to complete the table

<colgroup><col><col><col span="2"><col><col span="15"></colgroup><tbody>
</tbody>

 

[Dunks411]

Hey Thanks for the reply!

I am struggling to get my head around this will put it in a sheet and give it a go