Hello,

I need some help with a lookup formula. In my data set, I have some
customers shown multiple times - and I don't want to delete multiple listings
in my data. I also have a column looking at the data and I want my formula to
return the maximum amount for a particular customer.

Example:
Customer (A1) Sales (B1) Max Cust Value - lookup formula (C1)
Microsoft $0 $200
Intel $10 $20
Microsoft $100 $200
Intel $20 $20
Microsoft $75 $200
Microsoft $200 $200

Formula result I'm looking for: In cell C2, I want a formula that would look
at all the Microsoft values and return the maximum value which in this case
would be $200. Similarly, I'd want cell C3 to return $20 for the max Intel
value.

Thanks in advance for your help.
-Scott

try in c1
=max(if(A1:A100=a1,B1:B100,0)
entered as a array (control shift enter)then copy this down

"Scott" wrote:

Hello,

I need some help with a lookup formula. In my data set, I have some
customers shown multiple times - and I don't want to delete multiple listings
in my data. I also have a column looking at the data and I want my formula to
return the maximum amount for a particular customer.

Example:
Customer (A1) Sales (B1) Max Cust Value - lookup formula (C1)
Microsoft $0 $200
Intel $10 $20
Microsoft $100 $200
Intel $20 $20
Microsoft $75 $200
Microsoft $200 $200

Formula result I'm looking for: In cell C2, I want a formula that would look
at all the Microsoft values and return the maximum value which in this case
would be $200. Similarly, I'd want cell C3 to return $20 for the max Intel
value.

Thanks in advance for your help.
-Scott

Try...

C2, copied down:

=MAX(IF($A$2:$A$7=A2,$B$2:$B$7))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Scott" wrote:

Hello,

I need some help with a lookup formula. In my data set, I have some
customers shown multiple times - and I don't want to delete multiple listings
in my data. I also have a column looking at the data and I want my formula to
return the maximum amount for a particular customer.

Example:
Customer (A1) Sales (B1) Max Cust Value - lookup formula (C1)
Microsoft $0 $200
Intel $10 $20
Microsoft $100 $200
Intel $20 $20
Microsoft $75 $200
Microsoft $200 $200

Formula result I'm looking for: In cell C2, I want a formula that would look
at all the Microsoft values and return the maximum value which in this case
would be $200. Similarly, I'd want cell C3 to return $20 for the max Intel
value.

Thanks in advance for your help.
-Scott