Get information corresponding to max value

Formula

=INDEX(range,MATCH(MAX(range1),range1,0)

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:

=INDEX(D5:D9,MATCH(MAX(D5:D9),D5:D9,0))

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar