SOLVED

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

Copper Contributor

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

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!

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

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

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)``

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

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

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

You're welcome. Have a great weekend!

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

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

An alternative could be FILTER.

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

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!
1 best response

Accepted Solutions
best response confirmed by ReneeBay5000 (Copper Contributor)
Solution

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

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)``