find largest value in an array if value in first column matches specified value

Question

I'm trying to find the largest or max value in an array/range (E44:I205) among rows with values in column D (D44:D2015) that match a word. For instance:

D E F G H I
Cheetah Cat 0 1 2 3 4 
Tiger Cat 1 1 2 3 4 5
Dog 0 0 1 2 3

Among the rows with the word "*"&"cat", I want to find the max value. In this example, the formula should = 5. I've tried the following formula, but it just returns the first instance of "cat" and the associated max value in that row.

=LARGE(IF($D$25:$D$205="*"&"cat",$E$44:$I$205,),1)

Any help is much appreciated!

Answer 1

Use:

=AGGREGATE(14,6,E25:I205/(RIGHT(D25:D205,3)="cat"),1)