Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH

This is an advanced lookup tutorial that uses the INDEX and MATCH functions.

This is a step-by-step tutorial but, if you want more information on INDEX/MATCH lookups and how to do them, check out our tutorial on that here: Index and Match Lookup in Excel

Steps to find the Min/Max in a Range and Return a Value Based on That in Excel

In this example, we will return the site that has the highest rank.  This means we will return a value from the Site column based on what we find in the Rank column.

1. Type =INDEX(
   
   
2. Select the column that contains the data you want to return, in this case the Site column.
   
3. Type a comma to go to the next argument and then type MATCH(
   
   
4. Now, type MAX(
   
   This is what allows us to return the site that has the highest or MAX rank.  If you want to return the site with the lowest rank, type MIN( here.
5. Select the column of data that contains the values from which you want to find the maximum, in this case the Rank column.
   
   Make sure that this column of data is exactly the same height as the first column of data that you selected in Step 2.
6. Type a closing parenthesis to close the MAX function. Then type a comma to move to the next argument.
   
7. Select the same column of data that you selected in Step 5.
   
8. Type a comma to move to the next argument and then type a 0 (zero).
   
   This ensures that only an exact match will be made when the lookup runs.
9. Type two closing parentheses.
   
10. Hit Enter and that's it!
    

You can see that red.com is the highest ranking website, at 5, and that is exactly what we wanted to return.

Here is the final formula:

Notes

This seems complex and it is for sure not simple but, once you do this a few times, it will become quite easy for you to remember.  Remember that we are just nesting a couple functions within another function.  Follow the steps above carefully and everything should work well for your data set.

Once you understand the basics of the regular INDEX MATCH lookup, this will be easy since we are only adding one more function, MAX or MIN.

At the top of the tutorial there is a link to a helpful tutorial on INDEX and MATCH that will further explain how to perform lookups like this in Excel.

Make sure to download the accompanying worksheet so you can see and use this formula in Excel.