I tried this and it also works so thanks!

"Teethless mama" wrote:

=SUMPRODUCT(MAX(($B$2:$B$9=E2)*$C$2:$C$9))

Just press ENTER


"MLS" wrote:

Hi,

I am trying to get a max age from a range of cells, however the result is
depending on the value in another cell. The range looks like this:

ID BU Age
11348 JPU 44
12479 FKG 46
12487 LPS 27
14539 LPS 32
17583 FKG 24
39175 LPS 34
45867 JPU 42
74583 FKG 39

I need a formula that will give me the max age in the following way:
FKG 46
JPU 44
LPS 34

Is there a way to do this without using a pivot table?

Many thanks,
MLS