In Numbers, is there a way to get the row instead of the value when using a MAX or MIN Function

numbers

In Numbers, is there a way to get the row instead of the value when using a MAX or MIN Function?

For example, let's take this sheet:

bread  10
cheese 20
meat   35

What I am trying to do is to show the most expensive food by comparing all price values, getting the row number of the MAX value and then print the respective food on that line.

Best Answer

Using INDEX(MATCH) this can be easily accomplished.

Formulas

This formula returns the food label:
=INDEX(Foods, MATCH(MIN(Cost),Cost))
Min formula returning food label

  • The INDEX function returns the value in the cell located at the intersection of the specified row and column within a collection of cells or from an array returned by an array function.
  • The MATCH function returns the position of a value within a collection.

This formula returns the Row number:
=ROW(INDEX(Foods,MATCH(MIN(Cost),Cost)))
Row number function

  • The ROW function returns the row number of the row containing a specified cell.

Replacing MIN with MAX returns the row with the Max result.
Formula results

Further information on these functions can be found in the Functions reference.

ROW

The ROW function returns the row number of the row containing a specified cell.

ROW(cell)
• cell: An optional cell reference to a single table cell. The referenced cell that can contain any value, or be empty. If cell is omitted, as in =ROW(), the function returns the row number of the cell that contains the formula.

Examples
=ROW(B7) returns 7, the number of row 7.

=ROW() returns the absolute row number of the cell containing the function

Related Question