index/match duplicate values.

[dan_l]

So here's what I've got:


1. Source is one of those office living spreadsheet nightmares.

2. I'm building a report on the back of it. It makes use of sumproduct, vlookup, and a few other goodys to allow a person to specify a month and a year, and produce a corresponding report.


One of my result sets:


Name|Sales

Mark 500

Tom 800

Chandoo 9000

Ed 800

Dan 400

Steve 100


I really only want to show/chart the top 5.


So:

large(b1:ba6,1) //returns 9000

large(b1:b6,2) //returns 800

large(b1:b6,3) //returns 800


That in mind:

=INDEX($A$1:$A$4,MATCH(LARGE(B1:B6,1),$B$1:$B$6,0)) // returns Chandoo

=INDEX($A$1:$A$4,MATCH(LARGE(B1:B6,2),$B$1:$B$6,0)) // returns Tom

=INDEX($A$1:$A$4,MATCH(LARGE(B1:B6,1),$B$1:$B$6,0)) // Returns Tom !!!!!! I want it to return Ed


Obviously, I see what the problem is here. Both guys have the same value, and that's all the index is looking at. But how do I make it work?


I think I'm going to experiment with:

if(=INDEX($A$1:$A$4,MATCH(LARGE(B1:B6,1),$B$1:$B$6,0))=resultcell1,=INDEX($A$2:$A$4,MATCH(LARGE(B2:B6,1),$B$2:$B$6,0)) but I have a feeling that's going to fail hard too.

 

[Clarity]

Have a look at this post from Chandoo. The comments section covers duplicates in this way. In summary add a fraction (eg row number/1000) to the value):


http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

 

[dan_l]

Thank you. It worked.