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

Occasional Contributor



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!










5 Replies
best response confirmed by ReneeBay5000 (Occasional Contributor)

Adjust the back end of the range as needed.

Place in K2 and fill down.



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



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!