Dynamic Range Formula or Non Contiguous Single Column Based on Value of 2nd Column?

After trying a few options and searching for the last couple hours can't seem to find a way to have a dynamic named range that will select the cells in Column B if the corresponding value in Column A is equal to a specific value ("Y"). Any info on how I could accomplish this is appreciated. FYI, I am generating the dynamic named range specifically to identify cells to clear the values from in a macro. Thanks.

Cross Post on MrExcel

Assuming data starts on row 1

in C1
=IFERROR(INDEX($B$1:$B$1000,AGGREGATE(15,6,ROW($B$1:$B$1000)/(($A$1:$A$="Y")),ROWS(A$1:A1))-(1-1),1),"")
and copy down the column

Change the red 1s to whatever row the data starts on

Originally Posted by Special-K

Assuming data starts on row 1

in C1
=IFERROR(INDEX($B$1:$B$1000,AGGREGATE(15,6,ROW($B$1:$B$1000)/(($A$1:$A$="Y")),ROWS(A$1:A1))-(1-1),1),"")
and copy down the column

Change the red 1s to whatever row the data starts on

What you posted definitely works to generate an actual dynamic contiguous range which lists all of the values that meet the criteria with one minor typo correction:

=IFERROR(INDEX($B$1:$B$1000,AGGREGATE(15,6,ROW($B$1:$B$1000)/(($A$1:$A$1000="Y")),ROWS(A$1:A1))-(1-1),1),"")

However, I should have been more clear in my original post. I am actually looking to generate a Dynamic NAMED Range that will simply select the cells. I then intend to reference this dynamic named range in a macro to delete the data. Sorry about the confusion.