Need Help with Complex??? Formula

Hi

I just created a workbook for keeping track of local horse sale results. What I want to be able to do is to be able to tell what the top selling locally-bred horse is.

I created a spreadsheet that has just a list of the local stallions.

There are 2 sheets for each sale: Results and Breakdown.

What I want to be able to do is to search through the Results sheet for the highest selling horse whose sire is in the list of local stallions, and display that on the Breakdown sheet.

If you would like to see what I mean, go to http://atlanticstallions.co.nr/sales/2005_classics.htm

The first section on the page is the sale results. The second section is the breakdown. As you can see, I have statistics there already, but I have put those in manually. I made a formula for the overall top seller, but can't get the one working for the top locally-bred.

I think the only information needes is this:
STUD LIST --> Studs!A1:A56

HORSE NAMES --> Result!B2:B124
SIRE NAMES --> Result!D2:D124
SALE PRICE --> Result!H2:H124

I've played around with these endlessly, but just can't get it to work.

Any help is appreciated.

Here is the first approach that came to mind. I'm usually not a fan of helper columns, but this is a case where it simplifies greatly.

On Stud List, enter the following formula in B1 and then fill down to B56:
{=max((Results!$D$2:$D$124=A1)*Results!$H$2:$H$124)} This is an array formula, so you have to hit Ctrl+Shift+Enter when leaving the cell instead of just Enter. This formula will return the highest sale for each sire.

Next, on the breakdown sheet enter the following formula:
=INDEX(Result!B2:B124,MATCH(MAX(Studs!B1:B56),Result!H2:H124,0),1)
This will return the Horse Name, you could switch the first cell reference to D to get the Sire name, and you just need the MAX formula to grab the sale amount.

As I said, this isn't usually how I would approach this, but the alternative is very long and complicated formula (unless I am missing something. Hopefully this helps - Chad


STUD LIST --> Studs!A1:A56

HORSE NAMES --> Result!B2:B124
SIRE NAMES --> Result!D2:D124
SALE PRICE --> Result!H2:H124

Thanks cv.

I took your reply, worked with it a little (I never even realized the way I did it until I actually did it), and got it working.

I was trying to make it too complex. All the data I needed was in the breakdown sheet, so I just had to make sure that the stud was in the list.