Copy whole row into different sheet if specific text found in Column.

[strongman86]

Lads,
Hope you can help me. I have Table in Sheet1 consisting of 100's of rows and about 20 columns similar to below. I need formula in Sheet2 that looks through Column A Sheet1 and if it finds
APPLE IN CELL then it needs to copy whole row into Sheet2 and same for PEAR. This need to be in the order they appear in Sheet1. Thanks.


Sheet1

	A	B	C	D
1	APPLE	UK	10	WEEK1
2	BANANA	SPAIN	15	WEEK2
3	ORANGE	SPAIN	20	WEEK2
4	APPLE	IE	15	WEEK1
5	LEMON	US	10	WEEK3
6	PEAR	US	20	WEEK5

<tbody>
</tbody>

Sheet2

	A	B	C	D
1	APPLE	UK	10	WEEK1
2	APPLE	IE	15	WEEK1
3	PEAR	US	20	WEEK5
4
5
6

<tbody>
</tbody>

 

[Special-K99]

So it just looks for APPLE or PEAR in Sheet1, nothing else?

 

[strongman86]

Yes mate.

 

[Special-K99]

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$D$6,AGGREGATE(15,6,ROW($A$1:$D$6)/((Sheet1!$A$1:$D$6="APPLE")+(Sheet1!$A$1:$D$6="PEAR")),ROWS(A$1:A1))-(1-1),COLUMN()),"")
copy down for as many rows as you have on Sheet1 and across to column D

 

[strongman86]

Thank you Special-K99