Transpose data to new sheet

[Jezza25]

Hi All,

I have 2 sheets on the first sheet is the following first 3 columns

1. Apple Yes
2. Banana
3. Pear Yes
4. Lemon Yes
5. Grape
6. Orange Yes

What I am looking for is on the second sheet I need a formula to only bring in the ones that have a Yes next to them so the second sheet would have the following in the first 4 rows

1. Apple
3. Pear
4. Lemon
6. Orange

thanks

Jezza

 

[Nathan_Hale]

Hi Jezza,

I solved for the number value then did a regular lookup off that number. I started with a 0 in A1 of the second sheet so I didn't have to bend the formula too much.

This is a CSE formula so you will need to hit Ctrl+Shift+Enter for it to work properly (braces{} will add themselves).

Solve for number starting in A2 of Sheet2:

{=INDEX(Sheet1!$A$1:$A$6,MATCH(1,(A1<Sheet1!$A$1:$A$6)*("Yes"=Sheet1!$C$1:$C$6),0))}

Once you have the number you can do an INDEX/MATCH or VLOOKUP to grab the fruit name.

NH