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.

## Leave a Comment