Vlookup

Hello,

I am using Excel 2007. I posted a very similar problem to this quite recently which NBVC kindly helped out with (now marked 'Solved') but i'm looking for something slightly different now.

I have attached a workbook. Sheet 1 shows how my data is now and Sheet 2 shows how I want it to be after (if I was reviewing the 'Bananas' column (F:F).

I need a formula, or formulas, which looks at 'Sheet 1' and reviews the 'Bananas' column (F:F) and puts the names of the fruit (in range A2:A6) which have '1's in the F column into Sheet 2 under the heading 'First List'. In this example the formula would find 'Oranges' and 'Pears'.

The 'Second List' on Sheet 2 is the list of fruits that have '1s' for those fruits found in the 'First List'. So for example 'Oranges' (ON ROW 3) has two 1s - these are 'Apples' and 'Bananas'. These go in the 'Second List'

Can anyone help with this?

Thanks!!!!

Similar to the other thread... with the help of helper columns, the first 3 formulas remain the same...

In A4:

=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(INDEX(Sheet1!$B$2:$F$6,0,MATCH($A$1,Sheet1!$B$1:$F$1,0))=1,ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),ROWS($A$4:$A4))),"")

CSE confirmed and copied down

in B4:

=IF(A4="","",COUNTIF(INDEX(Sheet1!$B$2:$F$6,0,MATCH(A4,Sheet1!$B$1:$F$1,0)),1)+B3)

copied down

in C4:

=IF(ROWS(A$4:A4)>MAX($B$4:$B$8),"",INDEX($A$4:$A$8,MIN(IF(ROWS(A$4:A4)<=$B$4:$B$8,ROW($B$4:$B$8)-ROW($B$4)+1))))

CSE confirmed and copied down

D4 changes to:

=IFERROR(INDEX(Sheet1!$A$2:$A$6,SMALL(IF(INDEX(Sheet1!$B$2:$F$6,MATCH(C4,Sheet1!$B$1:$F$1,0),0)=1,COLUMN(Sheet1!$B$2:$F$6)-COLUMN(Sheet1!$B$2)+1),COUNTIF(C$4:C4,C4))),"")

CSE confirmed and copied down