Hi all,
I am trying to get excel to return index match max values for me only when the column x values equal a certain number. I initially thought nesting the index match within an if statement would work, but the if does not limit the index match, it only checks to see that column x has the values specified. For example:
=IF($H$5:$H$1048576=5,(INDEX($D$5:$D$1048576,MATCH(MAX($I$5:$I$1048576),$I$5:$I$1048576,0),0)),0)
My current formula checks column H for the value 5 - if there is a 5, the index formula will return the max. However, I only want it to return the max value within all the 5s. Any ideas how I can do this?
Any response is appreciated. Thanks!
I am trying to get excel to return index match max values for me only when the column x values equal a certain number. I initially thought nesting the index match within an if statement would work, but the if does not limit the index match, it only checks to see that column x has the values specified. For example:
=IF($H$5:$H$1048576=5,(INDEX($D$5:$D$1048576,MATCH(MAX($I$5:$I$1048576),$I$5:$I$1048576,0),0)),0)
My current formula checks column H for the value 5 - if there is a 5, the index formula will return the max. However, I only want it to return the max value within all the 5s. Any ideas how I can do this?
Any response is appreciated. Thanks!