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

[ReneeBay5000]

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!

[#2 · Accepted 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)

[#3]

Thanks so much! This helps a lot and will save me a lot of time and headache!

[#4]

You're welcome. Have a great weekend!

[#5]

@ReneeBay5000 

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

An alternative could be FILTER.

[#6]

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!