Maximum Based on a Single Criteria
The following formula returns the maximum value from B2:B10, where the corresponding value in A2:A10 equals the value in D2...
=MAX(IF(A2:A10=D2,B2:B10))
Note that the formula needs to be confirmed with
CONTROL+SHIFT+ENTER. If done correctly, Excel will
automatically place curly
braces {...} around the formula.
Based on the sample data, the formula returns 80.
Sample Workbook: Download