Lookup up data in a certain column and provide whole row.

[rossmolden]

Hi All

I need to find a formula which looks up value "4" in column C and displays the whole row, then for the next row down it does the same for the next time "4" is displayed in that column.

Original Data:

A	B	C	D
row 2 data	row 2 data	2	row 2 data
row 3 data	row 3 data	4	row 3 data
row 4 data	row 4 data	6	row 4 data
row 5 data	row 5 data	4	row 5 data
row 6 data	row 6 data	3	row 6 data

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

Formulated Data:

A	B	C	D
row 3 data	row 3 data	4	row 3 data
row 5 data	row 5 data	4	row 5 data

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Sheet1, A:D, houses the sample data.

Sheet2, A:E, houses the processing...

A1: 4

A2: Idx, B2:E2 houses the headers A1:D1 of Sheet1.

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(Sheet1!$C$2:$C$6=$A$1,
  ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1),ROWS($A$3:A3)),"")

B3, just enter, copy across as far as needed, then copy down:

=IF($A3="","",INDEX(Sheet1!A$2:A$5,$A3))

 

[rossmolden]

Sorry, this doesn't make sense to me. I am not very experienced with excel, any chance you could provide an excel template file, or layout exactly what I need to enter into each cell?

 

[Aladin Akyurek]

Sorry, this doesn't make sense to me. I am not very experienced with excel, any chance you could provide an excel template file, or layout exactly what I need to enter into each cell?

See the workbook that implements the set up of post #2 using the sample (!) you posted:
https://dl.dropboxusercontent.com/u/65698317/rossmolden%20Lookup%20up%20data%20in%20a%20certain%20column%20and%20provide%20whole%20row.xlsx