Find the Min or Max value in a range and, based on that, return a value from another range.
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
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.
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:
=INDEX(E5:E9,MATCH(MAX(D5:D9),D5:D9,0))
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.