Restricting Index Match to search only within a range of values

[yolkrule]

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!

 

[JoeMo]

This is an array formula - must be confirmed with ctrl+shift+enter, not just enter.

=INDEX($D$5:$D$1048576,MATCH(MAX(IF($H$5:$H$1048576=5,$I$5:$I$1048576,0)),$I$5:$I$1048576,0),)

 

[yolkrule]

Worked like a charm thanks joe

 

[Aladin Akyurek]

What happens if the MAX value occurs more than once in $I$5:$I$1048576 corresponding to $H$5:$H$1048576 equaling 5?