Example and Justification
Figure 1. Get information corresponding to the maximum value.
To look up information and identified the max value in a range, you can utilize a formula that combines the MAXIMUM, INDEX, and MATCH function. In the figure above, cell I6 contains:
that gives the result value to be 120, that represent the of the most costly Goods among the listed property.
How this formula functions
The MAX function first withdraw the max value ranging from D5 to D9.
For this situation, that result is 120.
The result is then provided to the MATCH function as the lookup value. The lookup column is a similar range B5:B9, and the match type is set to “correct” with 0.
In view of this figure above, the MATCH function finds and gives the relative position of the max value in the range.
The number 1 is provided to INDEX as “position” alongside the range B5:B9, provided as the lookup column.