< Go Back

Find the position of the max value in a list

The Excel MATCH function is used to locate the position of a lookup value in a row, column, or table. The Excel MAX function returns the largest numeric value in a range of values. To get the position of max value in the list (a range or table or a row), you can use the MAX function together with the MATCH function.  

Formula

=MATCH(MAX(cell_range),cell_range,0)

Explanation

The formula incorporating Excel MATCH and MAX functions to get the position of max value in list uses the following argument:

  • cell_range (required): The table, list or a row from which the position of max value needs to be extracted.

This works as follows:

  • The MAX function initially finds the highest value from the specified range.
  • This particular number is then sent to the MATCH function as a lookup value. The lookup array is actually the same range and the match type is set exactly to 0.
  • With these arguments, MATCH function finds the highest value inside the specified range and tends to returns the relative position of that value in the specified range.

Example 1

In this example, we have a list of several commodities. Their prices are mentioned and we need to find the commodity with the maximum price. To get the position of max value in a list, we can use the following combination of the Excel MATCH and MAX functions;

=MATCH(MAX(C5:C9),C5:C9,0)

This works as follows:

  • The MAX function initially finds the highest value from the specified range which comes out to be 225.
  • This number is then sent to the MATCH function as a lookup value. The lookup array is the same range (C5:C9) and the match type is set to exactly to 0.
  • With these arguments, MATCH function finds the highest value inside the specified range and tends to returns the relative position of that value in the specified range which is 4.

 

Figure 1. Example of how to get the position of max value in list 1

Notes

  • In this example, the position matches to a comparative row number, but in a range that is horizontal, the position would certainly match to a relative column number. In case there are duplicate values, this formula tends to return the position of the initial match, which is also the default behavior of the MATCH function.
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