Home

Tough formula for index/match/vlookup/small and array.....I can't solve!

%3CLINGO-SUB%20id%3D%22lingo-sub-861860%22%20slang%3D%22en-US%22%3ETough%20formula%20for%20index%2Fmatch%2Fvlookup%2Fsmall%20and%20array.....I%20can't%20solve!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861860%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20usually%20pretty%20good%20with%20formulas%20but%20can't%20figure%20this%20one%20out!%20I%20have%20a%20bunch%20of%20data%20in%20the%20chart%20on%20the%20left%20that%20needs%20to%20be%20reorganized%20into%20the%20format%20in%20the%20middle.%20The%20ultimate%20output%20should%20look%20like%20the%20one%20on%20the%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20you%20can't%20really%20use%20Vlookup%20or%20index%2Fmatch%20because%20there%20is%20no%20unique%20value%20(i.e.%20there%20are%20two%20pairs%20of%20Run1%20and%20QC%20Low....that%20have%202%20different%20values.%20In%20other%20places%20I%20have%20used%20an%20array%2C%20but%20that%20doesn't%20really%20work%20because%20the%20column%20sizes%20are%20different.%20I've%20also%20used%20an%20array%20with%20SMALL%20to%20sort%20the%20data....this%20works%20well%2C%20but%20that%20would%20move%20values%20from%20Dil%20and%20Run2%20(i.e.%2010%20and%2011)%20up%20two%20cells%20next%20to%20Run1%2C%20which%20isn't%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wonder%20if%20anyone%20has%20a%20unique%20solution%20to%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDATA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHOW%20TO%20POPULATE%20CHART%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHow%20it%20should%20be%20populated%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EQC%3C%2FTD%3E%3CTD%3EFile%3C%2FTD%3E%3CTD%3Ehow%20many%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHigh%3C%2FTD%3E%3CTD%3ELow%3C%2FTD%3E%3CTD%3EDil%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EHigh%3C%2FTD%3E%3CTD%3ELow%3C%2FTD%3E%3CTD%3EDil%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHigh%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELow%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELow%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHigh%3C%2FTD%3E%3CTD%3ERun01%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%3F%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHigh%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELow%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELow%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHigh%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDil%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDil%3C%2FTD%3E%3CTD%3ERun02%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-861860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862492%22%20slang%3D%22en-US%22%3ERe%3A%20Tough%20formula%20for%20index%2Fmatch%2Fvlookup%2Fsmall%20and%20array.....I%20can't%20solve!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862492%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20requirement%20is%20quite%20achievable%2C%20since%20you%20look%20for%20two%20different%20values%20for%20each%20category%20therefore%20you%20need%20to%20use%20two%20different%20formulas%20(one%20for%20minimum%20value%20%26amp%3B%20other%20for%20maximum%20value).%20You%20can%20try%20below%20formulas%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMIN(IF(%24A%243%3A%24A%2412%3DF%242%2CIF(%24B%243%3A%24B%2412%3D%24E3%2C%24C%243%3A%24C%2412)))%3C%2FP%3E%3CP%3E%3DMAX((%24A%243%3A%24A%2412%3DF%242)*(%24B%243%3A%24B%2412%3D%24E4)*%24C%243%3A%24C%2412)%3C%2FP%3E%3CP%3EPlease%20use%26nbsp%3B(Ctrl%20%2B%20Shift%20%2B%20Enter)%20as%20they%20are%20array%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20attached%20a%20sample%20file%20for%20your%20reference.%20Please%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
gms4b
Contributor

I am usually pretty good with formulas but can't figure this one out! I have a bunch of data in the chart on the left that needs to be reorganized into the format in the middle. The ultimate output should look like the one on the right.

 

The problem is that you can't really use Vlookup or index/match because there is no unique value (i.e. there are two pairs of Run1 and QC Low....that have 2 different values. In other places I have used an array, but that doesn't really work because the column sizes are different. I've also used an array with SMALL to sort the data....this works well, but that would move values from Dil and Run2 (i.e. 10 and 11) up two cells next to Run1, which isn't right.

 

I wonder if anyone has a unique solution to this?

 

Thanks,


Greg

 

DATA   HOW TO POPULATE CHART?     How it should be populated   
QCFilehow many  HighLowDil   HighLowDil
HighRun011 Run01???  Run0112 
LowRun012 Run01???  Run0143 
LowRun013 Run02???  Run025610
HighRun014 Run02???  Run028711
HighRun025           
LowRun026           
LowRun027           
HighRun028           
DilRun0210           
DilRun0211           

 

 

 

 

 

 

1 Reply

Hi @gms4b 

 

Your requirement is quite achievable, since you look for two different values for each category therefore you need to use two different formulas (one for minimum value & other for maximum value). You can try below formulas, 

 

=MIN(IF($A$3:$A$12=F$2,IF($B$3:$B$12=$E3,$C$3:$C$12)))

=MAX(($A$3:$A$12=F$2)*($B$3:$B$12=$E4)*$C$3:$C$12)

Please use (Ctrl + Shift + Enter) as they are array formula.

 

I have also attached a sample file for your reference. Please let me know if it works for you.

 

Thanks

Tauqeer

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies