Creating a top 5 list with conditionals

Hi all,

"New" guy to the forum here, as in "I've been lurking but not registering and posting".
I have a situation that I can't find any good examples for, nor do I find a working solution, so I hope someone with a good idea can help me.

I have a large dataset, a matrix of about 20000 lines and 25 columns. This is data I use to track a lot of different aspects and some of the statistics for it are presented on another sheet. There's one thing missing on this presentation; several top 5 lists that meets certain criteria. I have managed to do this with pivot tables and directly referencing the result in the presentation, but it's not an ideal way to do it.

The attached picture shows an analogy of what I try to achieve.

To describe the picture in text:
I want to return the "Sales ID" for the line that contains the maximum of "Sold for" if "Species" is "Cat". That way I can use VLOOKUP to fill in the other fields as I wish.
Next line should be similar, but it's the second highest value that meets the same criteria, and so on until I have a top 5 list (or top 10 for that matter).
Using pivot table with the filters I want makes this an easy task, so I would assume it's trivial to do it in a formula too, but for once I have to give up and try to look for fresh ideas on how to do it.

If anyone can suggest anything, please do so - it will be much appreciated

Thank you.

Hi,
Yes, I was thinking about that too, so here it is

Thank you for a prompt reply.

I never discovered the Aggregate function, nice to know

This formula works to some extent; it gives me the top 5 resulting values, but ideally I want to return the corresponding "Sales ID" (columns A) so I can lookup the name or anything else on that row based on the "Sales ID".

Maybe I misunderstood your example or I may have been unclear in what I try to achieve (?).
The "Species" will be given for each top 5 list (constant "Cat" in A33), while the top 5 will need a sales ID, a name and a value listed.

Thank you - that gave some meaningful results.
I applied it to my existing workbook with the live data, and it seemingly worked okay. After some double checks, I see that it will trigger a bit randomly when there are some duplicate data matching "Sold for" and "Species", but have different "Sold by".

I think this requires a better example from me, since the actual sheet have quite a few conditionals and the end result should really be the "Sales ID" so I can use VLOOKUP on it. Unfortunately the actual workbook is shock full of sensitive data, so I will create and attach a much better example later.

Anyway, thank you for your awesome help so far!

Hi,

I have now created a new example file that should be more representative for the dataset I'm working with. I have included an example result list (3 of them actually) and an additional sheet that shows how it could be solved with a pivot table.
Maybe it's best to have a hidden sheet that contains those pivot tables after all? That's what I've done before, but I think it's an improper way of doing it.

Hopefully Microsoft creates a streamlined function for this type of thing in the future, since I don't think I'm the only one needing such results all the time

Thank you so much, belinda200 and XLent!
The final solution seems to be working perfectly when implemented into my workbook. I did have make one minor adjustment since the SO-ID was a bit longer than shown in my example.

=ROUND(MOD(AGGREGATE(14,6,(orderlist[Price]+(orderlist[so-id]/10^7))/(orderlist[Transmission]="Manual")/(orderlist[Warranty]="Yes"),ROWS(A$39:A39)),1)*10^7,0)
was modified to
=ROUND(MOD(AGGREGATE(14,6,(orderlist[Price]+(orderlist[so-id]/10^8))/(orderlist[Transmission]="Manual")/(orderlist[Warranty]="Yes"),ROWS(A$39:A39)),1)*10^8,0)

So far it have passed my sanity checks, so hopefully this will be sufficient.
Now I have to analyze that formula and really learn what's going on. AGGREGATE with all its functions and inputs is currently a small puzzle to me, even though I had no issue implementing it.

Again, thank you for your super expertise