• 551K Members
• 6,636 Online
• 660K Conversations
SOLVED

## Index Match for Large Selection

Occasional 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!
7 Replies
Highlighted

# Re: Index Match for Large Selection

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.
Highlighted

# Re: Index Match for Large Selection

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

# Re: Index Match for Large Selection

You need an INDEX-SMALL array.

Highlighted
Solution

# Re: Index Match for Large Selection

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.

Highlighted

# Re: Index Match for Large Selection

Thank you @Patrick2788!
Highlighted

# Re: Index Match for Large Selection

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

# Re: Index Match for Large Selection

@Cami96 , you are welcome

Related Conversations
Matching Excel cells by column
ddelise in Excel on
3 Replies
Suggested input bar
kslai in Microsoft Forms on
2 Replies