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)

	a	b
1	Order	SKU
2	1	1
3	1	2
4	1	3

<tbody>
</tbody>

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.

	a
1	SKU
2
3
4

<tbody>
</tbody>

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)

	a	b
1	Order	SKU
2	1	1
3	1	2
4	1	3

<tbody>
</tbody>

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.

	a
1	SKU
2
3
4

<tbody>
</tbody>

Thanks for your time guys