Vlookup top ten

**Kidcarlosuk** · 06-06-2012, 10:02 AM

Hi all,

I have a summary page which shows total figures to do with outstanding debt owed to my company.

I use a database which just refreshes each month and a number of sumif, vlookup or whatever link it all through to the summary page. A while ago someone who receives the report asked me to add the top 10 overdue accounts as well. Because the customers can change each month the way i did this was to add a rank calculation to my data report and vlook'd up the rank. This all works fine and I'm happy with it.

I'm now looking to produce a different report for my international region and I want to top 10 by country. The problem I have is the report is all in one, so I will have UK, Spain, France all in one report, therefore If I rank it will rank by all countries. Is there anyway to rank by each country. A sort of "Rank if" function.. or is there are what of vlookuping just the top ten?

Thanks

Carl

**NBVC** · 06-06-2012, 10:11 AM

You can rank with criteria like so:

=SUMPRODUCT(--($A$2:$A$200=A2),--(B2<$B$2:$B$200))+1

copied down, where A2:A200 contain countries and B2:B200 contain values.

You can further id each rank to a country by adding the country id by concatenation.

e.g.

=A2&"_"SUMPRODUCT(--($A$2:$A$200=A2),--(B2<$B$2:$B$200))+1

copied down

Then you can lookup this new list for the country using INDEX/MATCH to lookup to the left...

e.g.

=INDEX($A$2:$A$200,MATCH(X1&"_"&1,$C$2:$C$200,0))

Where X1 contains the country, and the 1 represents 1st ranked... adjust as necessary to accomodate your workbook.

**Kidcarlosuk** · 06-06-2012, 10:44 AM

hi NBVC,

The first two parts works a treat and gives me exactly what I need. The concatination means I can use the same lookup as before which is great.

I've working through the Index formula, but have got a little lost. Will keep plugging away to work it, but I've got the result I need so thanks very much.

Best regards
Carl

**NBVC** · 06-06-2012, 10:56 AM

Again the X1 is the cell containing the Country you need (that would be the same as lookup value, if you had use VLOOKUP). You can change to suit

The C2:C200 is refererring to the range you put the helper ranking formula in....

So we want to concatenate the country with an underscore and rank number and lookup that string in C2:C200. If found, it will return the coinciding value from A2:A200... maybe that should have been B2:B200 to get the values...

=INDEX($B$2:$B$200,MATCH(X1&"_"&1,$C$2:$C$200,0))

... but hopefully you get the just of it.