Look Up Data with Microsoft Excels INDEX(), MATCH() and MAX() Functions
The technique of NESTING Microsoft Excel functions within one another allows for more dynamic and powerful formulas.
I got an excellent question the other day, in one of my Excel courses on Udemy, asking how to look up the largest value in a column, then return an associated value from the row that value was found in.
LOOKUP LARGEST VALUE
In the above example, we want to find the largest value in the YTD column (Column D). This can easily be accomplished using the MAX() function.
Using MAX(), this would return the value 33.50% found in cell D8.
In many cases, this is enough. But, sometimes finding the MAX numeric value doesn't provide the information needed. It may not be the numeric value that you are looking for but an associated value such as the MARKET NAME found in COLUMN A. In this case, we should return "RUSSIA", which is the associated market of the largest YTD value.
Although the MAX() function is great, it doesn't perform the necessary task by itself.
EXPANDING THE MAX FUNCTION WITH INDEX() AND MATCH()
In the above example, using the INDEX() function to search for a value in the A COLUMN, based on the results of the MATCH() function with a NESTED MAX() function, will return the associated MARKET NAME of the MAX YTD value.