finding the largest value for a name in a column and then returningthe result from a different column
 

Hello,

I've been struggling with this thing. Let's say I have a 3 column
table:

A1; B1; C1
CAR; PRICE; DEALER
toyota; $1,000; dealer1
toyota; $900; dealer2
honda; $1,200; dealer2
honda; $825; dealer1
bmw; $1,500; dealer1
bmw; $,1,000; dealer2

I need to enter a formula in D2 to return the dealer of the highest
price for the car selected in Cell D1. So if Honda is typed into cell
D1 then cell D2 would return "dealer2" because that is the dealer with
the most expensive honda.

Hope I explained this clearly enough so somebody can help me.

thank you in advance.

finding the largest value for a name in a column and then returning the result from a different column
 

In D1, eg: Honda
In D2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=INDEX(C2:C7,MATCH(MAX(IF(A2:A7=D2,B2:B7)),IF(A2:A 7=D2,B2:B7),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
Hello,

I've been struggling with this thing. Let's say I have a 3 column
table:

A1; B1; C1
CAR; PRICE; DEALER
toyota; $1,000; dealer1
toyota; $900; dealer2
honda; $1,200; dealer2
honda; $825; dealer1
bmw; $1,500; dealer1
bmw; $,1,000; dealer2

I need to enter a formula in D2 to return the dealer of the highest
price for the car selected in Cell D1. So if Honda is typed into cell
D1 then cell D2 would return "dealer2" because that is the dealer with
the most expensive honda.

Hope I explained this clearly enough so somebody can help me.

thank you in advance.