Finding top 2 values per group based on multiple criteria

I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.

As an Example, here's what I'm trying to do:


Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33


..and from the list above, I want to get the top two rated Fords, Dodges, and Chevy's.

I have a search that is giving me only the top rated across all Makes using Indirect/Match, but that isn't giving me the top two PER Make.

Since I've failed to come up with a working solution, I implore the Excel Function wizards here to help me . I very much appreciate it!

I should note that I'm looking for the result to look like: Bronco(64), Expidition(53) for Ford, etc.

Thanks again

Anyone have thoughts on this to help?

While I dont assure that my technique is the best or most efficient method, it does work. Fiddle with it, and see if you can make it fit your application needs. (Note, this will work as long as the same manufacturer does not have equal ratings for two cars. If that is the case, we have to work on it a bit)

BigBas, I haven't dug into the function itself a lot yet, but this is just awesome. THANK YOU very much! From the result table, this looks exactly like what I'm trying to accomplish and have been failing at for days here. Too great. I'll try to incorporate this into my larger workbook and let you know once I merge and digest what you've shown me here.

Based on BigBas sample workbook try use this formula below it will account for duplicates.

Input formula in cell F2 copy across and copy down.

=INDEX($B$2:$B$11,MATCH(LARGE(IF($A$2:$A$11=F$1,$C$2:$C$11-ROW($C$2:$C$11)/10^5),ROWS(F$2:F2)),$C$2:$C$11-ROW($C$2:$C$11)/10^5,0))


The formula is an-array must hold down:

Ctrl,Shift,Enter

You are all excellent. I haven't even digested the first solution yet and you've provided more options and handled the duplicate rating issue.

Thanks again and great job!

I'll still try to get to review/implement this after my calls are done.

I was working on incorporating this into my actual workbook and realized that I wasn't complete in defining my situation. I actually forgot a level, which is very important . I am working on a skills matrix, but tried to convert my sheet to a "Car" example for simplicity. Well, I forgot a level...given my prior example, I would need a column for "Vehicle" so I can also accommodate Bikes, Motorcycles, etc.

So, I have taken the working copy that Vane sent (which added support for first and second highest rankings) and added a column for completeness and accuracy.

..Just to make sure I don't confuse this again, I also added the columns for the ACTUAL spreadsheet I'm working with for tracking the skills I'm working on.

I'm attaching the sheet, but these are the actual columns:
For my actual application
Skill Function Name Rating
C QA Joe 23
C QA Mike 14
C QA Betty 30
C Devt Tim 86
C Devt Fred 79
C Devt Sally 90
C Support Gene 0
C Support Bill 10
C Support Mary 25
Oracle QA Joe 85
Oracle QA Mike 62
Oracle QA Betty 81
Oracle Devt Tim 45
Oracle Devt Fred 79
Oracle Devt Sally 22
Oracle Support Gene 90
Oracle Support Bill 84
Oracle Support Mary 55

I have been banging away at this and realized this about an hour ago. I'd appreciate someone extending the current sheet to include this new level and give the same output. Basically, highest two people in each function for each skill.

Thanks beforehand
Russ

I have attached a sample fixing the problem ( I think).

I have made a modification, and I hope I did not over do it. THe way you have it setup, you only had it setup for one person per skill. I added an extra C row, and an extra Oracle row, as you will see.

Let me know if I can provide any further assistance.