Forum Discussion
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!
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)
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!
- 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!
