In the example shown, the formula in I5 is: Which returns the number 4, representing the position in this list of the the most expensive property. In this case, that value is 849900. This number is then supplied to the MATCH function as the lookup value. The lookup_array is the same range C3:C11, and the match_type is set to “exact” with 0. With those arguments, MATCH locates finds the maximum value inside the range and returns the relative position of the value in that range. In this case, the position corresponds to a relative row number, but in a horizontal range, the position would correspond to a relative column number. Note: in case of duplicates (i.e. two or more max values that are the same) this formula will return the position of the first match, the default behavior of the MATCH function.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
