# Restricting Index Match to search only within a range of values

#### yolkrule

##### New Member
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!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### JoeMo

##### MrExcel MVP
This is an array formula - must be confirmed with ctrl+shift+enter, not just enter.
Code:
``=INDEX(\$D\$5:\$D\$1048576,MATCH(MAX(IF(\$H\$5:\$H\$1048576=5,\$I\$5:\$I\$1048576,0)),\$I\$5:\$I\$1048576,0),)``

#### yolkrule

##### New Member
Worked like a charm thanks joe

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

Replies
3
Views
513
Replies
12
Views
344
Replies
7
Views
221
Replies
2
Views
336
Replies
3
Views
173

1,195,687
Messages
6,011,153
Members
441,589
Latest member
tommatwalker

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back