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

S

sergio.pringle

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.
 
M

Max

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:A7=D2,B2:B7),0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparing 2 columns from 2 different Worksheet and Returing Value of a 3rd column 2
Need formulas for searching for a value in a table and returning row/column name 7
Find values across a row and return a text value from the same column 3
How to find the largest change of values in a column 5
Check for values in column A and if found then column B must have value. 3
match row, then match column, then get header for that column? 1
Returning other cells with the same value in a column, but different row. 1
How do I make a formula work for all the cells in a column? 4

Top