MAX Value

[KREJ]

Good afternoon,

Hoping for some help here. I have 3 Columns of Data:

Customer Number | Combined Customer Number | Customer Rating
12345 | 67890 | 50
23456 | 67890 | 60
34567 | 67892 | 40
45678 | 67892 | 45
56789 | 67892 | 70

I would like to search on the Combined Customer Number field and if this number is the same, return the MAX value of the Customer Rating.

In the example above, I would want Combined Customer Number 67890 to return 60 and Combined Customer Number 67892 to return 70.

I have tried using {=MAX(IF(B2=B2:B6,C2:C6))}, but I'm getting the MAX Value from the entire list.

Thanks!

 

[Luke M]

Welcome to the forum KREJ!
Hmm. Are you sure formula is correct? It is working in my workbook. Absolute references used so that I could copy down.

 

[KREJ]

Perfect Luke! The absolute reference was the missing piece. Thanks for your quick reply!

 

[Somendra Misra]

Hi Krej,

Keeping the some part of @Luke M formula and changing the MAX function to AGGREGATE function can avoid use of CSE.

Like this
=AGGREGATE(14,6,(B2=$B$2:$B$6)*($C$2:$C$6),1)
in D2

Just enter with ENTER and CSE required.

Note: AGGREGATE function only works on 2010+.

Regards,