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

#### rossmolden

##### New Member
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>

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:
Rich (BB code):
``````=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:
Rich (BB code):
``````=IF(\$A3="","",INDEX(Sheet1!A\$2:A\$5,\$A3))
``````

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?

Replies
1
Views
252
Replies
3
Views
249
Replies
1
Views
62
Replies
7
Views
121
Replies
2
Views
243

1,216,375
Messages
6,130,243
Members
449,568
Latest member
mwl_y

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back