Index Match for Large Selection

[Cami96]

Hey all! I've been messing with this and can't seem to get the answer I'm needing. I have a sheet that contains questions, with ranking drop downs. I have a separate sheet which I would like to pull any questions ranked as "Needs Improvement". I can manage to pull one at a time using
=('Audit Sheet'!A5:B512, Match("Needs Improvement",'Audit Sheet'!(C5:C512,0,2)
However, to avoid duplicates I have to allow the cell selection to move down. So the next cell would start at A9:B512 as a selection.

Is there a way to search the entire sheet and return all unique values from column B if column C is ranked "Needs Improvement"?

I appreciate the help!

[#2]

If this is basically a one-time situation, it strikes me (without actually seeing your sheet) that a quick and dirty manual solution might work. Not as elegant as a formula or function, but...

So just use the Data...Filter capability to display only those that have "Needs Improvement" and do a manual Copy....Paste to the other sheet.

[#3]

Apologies for not being more specific on this, it will need to be continuous.

[#4]

@Cami96 

You need an INDEX-SMALL array.

[#5 · Accepted solution]

@Cami96 

For such model

in G2 it could be

=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,1/($C$2:$C$20=$E$1)*(ROW($C$2:$C$20)-ROW($C$1)),ROW()-ROW($G$1) )),"")

similar in next column, and drag them down.

[#6]

Thank you @Patrick2788!

[#7]

Thank you for all the detail! I really appreciate it!

[#8]

@Cami96 , you are welcome