Using the value in a cell to define range in a formula?

Question

In spreadsheet I'd like to use MAX to return the highest value in a range. The range needs to be defined by the value in another column. So lets say the table has three columns: Animal|Type|Length, with each animal listed designated as Type: Mammal, Bird, or Fish, along with that animal's length in feet. So a row might say:

Dog | Mammal | 2

So, in plain English I'm looking for a formula that would ask "Which is the greatest length of all the animals designated as 'Mammal' in this table?" Same for the other Types, so that I would have a table like this, with a formula populating the Max Length cells:

Type | Max Length

Mammal | 6

Bird | 5

Fish | 6

Thanks - any help greatly appreciated.

Answer 1

You can use an "array formula" here, e.g. if your three columns are A (Animal), B (Type) and C (Length) you can use a formula like this in F2

=MAX(IF(B$2:B$100=E2,C$2:C$100))

where E2 contains the type

confirm formula with CTRL+SHIFT+ENTER and copy down