SOLVED
Home

Index Match for Large Selection

%3CLINGO-SUB%20id%3D%22lingo-sub-1102214%22%20slang%3D%22en-US%22%3EIndex%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102214%22%20slang%3D%22en-US%22%3EHey%20all!%20I've%20been%20messing%20with%20this%20and%20can't%20seem%20to%20get%20the%20answer%20I'm%20needing.%20I%20have%20a%20sheet%20that%20contains%20questions%2C%20with%20ranking%20drop%20downs.%20I%20have%20a%20separate%20sheet%20which%20I%20would%20like%20to%20pull%20any%20questions%20ranked%20as%20%22Needs%20Improvement%22.%20I%20can%20manage%20to%20pull%20one%20at%20a%20time%20using%3CBR%20%2F%3E%3D('Audit%20Sheet'!A5%3AB512%2C%20Match(%22Needs%20Improvement%22%2C'Audit%20Sheet'!(C5%3AC512%2C0%2C2)%3CBR%20%2F%3EHowever%2C%20to%20avoid%20duplicates%20I%20have%20to%20allow%20the%20cell%20selection%20to%20move%20down.%20So%20the%20next%20cell%20would%20start%20at%20A9%3AB512%20as%20a%20selection.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20search%20the%20entire%20sheet%20and%20return%20all%20unique%20values%20from%20column%20B%20if%20column%20C%20is%20ranked%20%22Needs%20Improvement%22%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20appreciate%20the%20help!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1102214%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102238%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102238%22%20slang%3D%22en-US%22%3EIf%20this%20is%20basically%20a%20one-time%20situation%2C%20it%20strikes%20me%20(without%20actually%20seeing%20your%20sheet)%20that%20a%20quick%20and%20dirty%20manual%20solution%20might%20work.%20Not%20as%20elegant%20as%20a%20formula%20or%20function%2C%20but...%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20just%20use%20the%20Data...Filter%20capability%20to%20display%20only%20those%20that%20have%20%22Needs%20Improvement%22%20and%20do%20a%20manual%20Copy....Paste%20to%20the%20other%20sheet.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102246%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102246%22%20slang%3D%22en-US%22%3EApologies%20for%20not%20being%20more%20specific%20on%20this%2C%20it%20will%20need%20to%20be%20continuous.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102374%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F513994%22%20target%3D%22_blank%22%3E%40Cami96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20an%20INDEX-SMALL%20array.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102430%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F513994%22%20target%3D%22_blank%22%3E%40Cami96%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20568px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164804i71FCB75BD984B1E9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20G2%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24A%242%3A%24A%2420%2CAGGREGATE(15%2C6%2C1%2F(%24C%242%3A%24C%2420%3D%24E%241)*(ROW(%24C%242%3A%24C%2420)-ROW(%24C%241))%2CROW()-ROW(%24G%241)%20))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Esimilar%20in%20next%20column%2C%20and%20drag%20them%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102480%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102480%22%20slang%3D%22en-US%22%3EThank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102489%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102489%22%20slang%3D%22en-US%22%3EThank%20you%20for%20all%20the%20detail!%20I%20really%20appreciate%20it!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102495%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20for%20Large%20Selection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102495%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F513994%22%20target%3D%22_blank%22%3E%40Cami96%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Cami96
Occasional Contributor
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
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
Apologies for not being more specific on this, it will need to be continuous.
Highlighted

@Cami96 

You need an INDEX-SMALL array.

Highlighted
Solution

@Cami96 

For such model

image.png

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
Thank you @Patrick2788!
Highlighted
Thank you for all the detail! I really appreciate it!
Highlighted

@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