Need Help with IF Statement Formula

[Digital2k10]

Hello,

I need some help with the following scenario. Please see the attachment for an illustration. Basically, I am looking to check the values in Sheet 1 ,Column E and for every cell which contains a value of '1', I'd like to pull the corresponding value from column A without leaving any blank or 'false' cells into Sheet 2, Column A, thus completely ignoring the Sheet 1, Column A values which have '0' as their corresponding value in Sheet 1, Column E. Ideally, I would also like to pull the 'data' for the corresponding Sheet 1, Column A values into Sheet 2 at the same time; however, I can do that in a separate step once I have filtered Sheet 1, Column A into Sheet 2, Column A based on the values in Sheet 1, Column E.

The above may be a little confusing. The attached example should help clear it up.

Thanks in advance. I am not sure if an IF statement is even the best option. Perhaps INDEX/MATCH? Thanks.

[benishiryo]

hi Digital2k10, welcome to the forum. try this array formula in Sheet2 A1:
=INDEX(Sheet1!A$1:A$16,SMALL(IF(Sheet1!$E$1:$E$16=1,ROW(Sheet1!$E$1:$E$16)-ROW(Sheet1!$E$1)+1),ROWS(A$1:A1)))

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it. copy down & across. change the range if necessary

[Digital2k10]

Thank you benishiryo! That worked perfectly.

One last thing...If it is not too much to ask, could you please explain the array formula from SMALL( onward? Thanks!