SOLVED

help with syntax/functions to select cells based on criteria of other cells

%3CLINGO-SUB%20id%3D%22lingo-sub-3466731%22%20slang%3D%22en-US%22%3Ehelp%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3466731%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20find%20cells%20in%20different%20columns%20and%20rows%20that%20are%20associated%20with%20a%20maximum%20value.%20I%20got%20as%20far%20as%20using%20the%20UNIQUE%20and%20MAX%2FIF%20functions%20to%20get%20that%20maximum%20value%2C%20but%20I%20can't%20figure%20out%20the%20best%20way%20(syntax%2Ffunctions)%20to%20also%20get%20the%20other%20cells%2Fvariables%20associated%20with%20that%20maximum%20value.%20Below%20is%20a%20screen%20shot%20with%20an%20example%20of%20what%20I've%20done%20so%20far%20and%20what%20I'm%20trying%20to%20achieve%20(the%20right%20side%20of%20the%20graphic).%20I'm%20investigating%20INDEX%2FMATCH%20and%20VLOOKUP%2C%20but%20my%20skills%20are%20%22limited%22...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F377519iB03FC2EA70E52446%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20alt%3D%22Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3466731%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-3466769%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3466769%22%20slang%3D%22en-US%22%3E%3CP%3EAdjust%20the%20back%20end%20of%20the%20range%20as%20needed.%3C%2FP%3E%3CP%3EPlace%20in%20K2%20and%20fill%20down.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DXLOOKUP(I2%26amp%3BJ2%2C%24A%242%3A%24A%24100%26amp%3B%24B%242%3A%24B%24100%2C%24D%242%3A%24F%24100)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3466880%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3466880%22%20slang%3D%22en-US%22%3EThanks%20so%20much!%20This%20helps%20a%20lot%20and%20will%20save%20me%20a%20lot%20of%20time%20and%20headache!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3466883%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3466883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1413219%22%20target%3D%22_blank%22%3E%40ReneeBay5000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(%24I2%26amp%3B%24J2%2CCHOOSE(%7B1%2C2%7D%2C%24A%242%3A%24A%2413%26amp%3B%24B%242%3A%24B%2413%2CD%242%3AD%2413)%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAn%20alternative%20could%20be%20VLOOKUP.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22select%20cells.JPG%22%20style%3D%22width%3A%20935px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F377524iF355DA0E7497FFA9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22select%20cells.JPG%22%20alt%3D%22select%20cells.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(D2%3AF13%3B(A2%3AA13%3DI2)*(B2%3AB13%3DJ2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20you%20work%20with%20Office365%20an%20alternative%20could%20be%20FILTER.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%202%20criteria.JPG%22%20style%3D%22width%3A%20918px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F377525i347019C2FFEE1A68%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22filter%202%20criteria.JPG%22%20alt%3D%22filter%202%20criteria.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3466887%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3466887%22%20slang%3D%22en-US%22%3EYou're%20welcome.%20Have%20a%20great%20weekend!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3467858%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3467858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1413219%22%20target%3D%22_blank%22%3E%40ReneeBay5000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(%24D%242%3A%24F%2413%3B(%24A%242%3A%24A%2413%3DI2)*(%24B%242%3A%24B%2413%3DJ2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAn%20alternative%20could%20be%26nbsp%3B%3CSPAN%3EFILTER.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22filter%20wih%202%20criteria.JPG%22%20style%3D%22width%3A%20915px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%20wih%202%20criteria.JPG%22%20style%3D%22width%3A%20915px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%20wih%202%20criteria.JPG%22%20style%3D%22width%3A%20915px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22filter%20wih%202%20criteria.JPG%22%20style%3D%22width%3A%20915px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F377596iE6E1F8080197B4AB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22filter%20wih%202%20criteria.JPG%22%20alt%3D%22filter%20wih%202%20criteria.JPG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3468197%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20syntax%2Ffunctions%20to%20select%20cells%20based%20on%20criteria%20of%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3468197%22%20slang%3D%22en-US%22%3EThanks!%20I%20appreciate%20having%20alternative%20options!%20I%20got%20your%20vlookup%20suggestion%20through%20email%20too%20that%20will%20be%20useful%20if%20I%20don't%20have%20access%20to%20365%20or%202021%20versions%20of%20excel!%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I need to find cells in different columns and rows that are associated with a maximum value. I got as far as using the UNIQUE and MAX/IF functions to get that maximum value, but I can't figure out the best way (syntax/functions) to also get the other cells/variables associated with that maximum value. Below is a screen shot with an example of what I've done so far and what I'm trying to achieve (the right side of the graphic). I'm investigating INDEX/MATCH and VLOOKUP, but my skills are "limited"...

 

Thanks for any help!

 

 

Excel365_GroupingSelectingCellsBasedOnMaxValueHelp.png

 

 

 

 

 

 

5 Replies
best response confirmed by ReneeBay5000 (New Contributor)
Solution

Adjust the back end of the range as needed.

Place in K2 and fill down.

=XLOOKUP(I2&J2,$A$2:$A$100&$B$2:$B$100,$D$2:$F$100)

 

Thanks so much! This helps a lot and will save me a lot of time and headache!
You're welcome. Have a great weekend!

@ReneeBay5000 

=FILTER($D$2:$F$13;($A$2:$A$13=I2)*($B$2:$B$13=J2))

An alternative could be FILTER.

filter wih 2 criteria.JPG

Thanks! I appreciate having alternative options! I got your vlookup suggestion through email too that will be useful if I don't have access to 365 or 2021 versions of excel!