Look up value which appears in column multiple times & display the values of cells on each row

[cfcMalky]

Hi folks

Got a wee lookup problem here, I want to search a column for a certain value which is displayed in a cell away from the table, the column will have more than 1 of this value, with different values in other cells on their rows, I want to display values from each row which contains my search value, This sounds double dutch I know lol, hopefully I can give a half decent example of what I'm gibbering about, Here goes nothing!:
Table 1 (Orders): (Underlined values are headers, the column has these names)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Order[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Table 1 basically tells you that 1 order has been placed, for 3 different items



Table 2 (Invoice): I would like to select order 1 in a cell away from this table (cell Z1 for example), and have this table display the 3 items from that order, in the rows in the SKU column, obviously i can't use vlookup because that would return N/A after finding the order number 3 times, with 3 different values over the search array.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your time guys

 

[Skybluekid]

What version are you using?

 

[cfcMalky]

What version are you using?

2010

 

[Skybluekid]

2010 version:
In Cell AA1 use =COUNTIF(Table1,Z1), this will give you a count of of items in the order.

In Table 2 A2 use =IF(ROWS($AB$1:AB1)>AA1,"",INDEX(Table1!B:B,AGGREGATE(15,6,ROW(Table2!B:B)-ROW(Table2!$B$1)+1/(Table1A:A=Z1),ROWS($AB$1:AB1))) Then copy Down

 

[cfcMalky]

I've got my nut in a twist here lol, because the table/cell numbers I used in the example are totally different from the ones on my workbook, when i try to edit my workbook
it all goes wrong, could i send you a workbook and you could edit it?

 

[Skybluekid]

Please see your PM

 

[mfexcel]

A pivot table would do the job for you efficiently.

Hi folks

Got a wee lookup problem here, I want to search a column for a certain value which is displayed in a cell away from the table, the column will have more than 1 of this value, with different values in other cells on their rows, I want to display values from each row which contains my search value, This sounds double dutch I know lol, hopefully I can give a half decent example of what I'm gibbering about, Here goes nothing!:
Table 1 (Orders): (Underlined values are headers, the column has these names)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Order[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Table 1 basically tells you that 1 order has been placed, for 3 different items



Table 2 (Invoice): I would like to select order 1 in a cell away from this table (cell Z1 for example), and have this table display the 3 items from that order, in the rows in the SKU column, obviously i can't use vlookup because that would return N/A after finding the order number 3 times, with 3 different values over the search array.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your time guys