Duplicate Formula with Multiple Data Cells

[Deleted]

Hi,

I'm looking to use a formula to transfer information from several columns to another table. The problem i'm having is i'm trying to transfer multiple rows with the same value. I.e. I want each row with "yes" in a given column to be transferred to the new table. I've used VLookup which returns the right information but only the first row. After that it duplicates the same row. I've attached a PDF which should help explain a little more. 

I'm sure there is a simple formula but being a somewhat newbie any help would be appreciated. 

Thanks 

[#2]

Hi,

To copy rows, on data sample like this,

in F2 you may use

=IFERROR(OFFSET($A$1,AGGREGATE(15,6,1/($A$2:$A$20="yes")*(ROW($B$2:$B$20)-ROW($A$1)),ROW()-ROW($F$1)),1),"")

and drag it down, same for other columns.

Similar with your model.

[#3]

You can also convert your data to Excel tables and it becomes dynamic ( the more rows are added or removed, the output will expand and collapse)  Sergei has provided you with a solution with formula.  The same result can be achieved using power query which is dynamic with Excel tables.

attached is a quick video on how to do this using Power Query.