Forum Discussion
Cami96
Jan 10, 2020Copper Contributor
Index Match for Large Selection
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!
=('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!
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.
7 Replies
Sort By
- Patrick2788Silver Contributor
You need an INDEX-SMALL array.
- Cami96Copper ContributorThank you Patrick2788!
- mathetesSilver ContributorIf 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.