return value from row where column equals "y"- problem with skipping rows

[Erin K]

I want to look down column D. If a cell if that column ="y", I want the formula to return the cell on column A in the same row. I want all these results in an unbroken list.

if I just use
=IF(D:D="y",A:A,"")

I get the results I want, but they display on the corresponding rows, skipping rows where the cell in D ="n". How can I solve this?

Thanks very much for any help!

[AlKey]

Your explanation a bit confusing but I think you looking for something like this.

=INDEX(A:A,SMALL(IF($D$1:$D$9="Y",ROW($D$1:$D$9)),ROWS(E$1:E1)))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B	C	D	E
1	45			y	45
2				N	15
3	15			y	54
4				N	56
5	45			N
6				N
7	54			y
8				N
9	56			y

[JBeaucaire]

A less formulaic approach:
1) click on row 1
2) Turn on the Data > Filter
3) Filter column D for "y"

All irrelevant rows are hidden until you change/remove the filter. You can copy the visible rows elsewhere if you really feel you need to, but I typically just deal with the results right there in the original data.

[Erin K]

That's exactly what I was looking for. Thank you both!!! I am using AlKey's suggestion but both would work.

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

[Erin K]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

Thanks again! As you've probably guessed, I'm really new to this.