Duplicates in an INDEX lookup

[Graemea]

Hi,


I have data with product names in A5:A14 and associated sales values in B5:B14.


I have used LARGE() to extract the top 5 sales values from column B


I now need to create a formula that will return the product name associated with each of the 5 largest values.


The problem is that there are duplicate values in B5:B14 which complicates the lookup.


After some searching I found the following formula, adapted for my data, which works perfectly but I'm baffled by how it works.


{=INDEX($A$5:$A$14,SMALL(IF($B$5:$B$14=$L5,ROW($B$5:$B$14)-ROW($B$5)+1),COUNTIF($L$5:L5,L5)))}


The references to column L is where I've extracted the top 5 largest values


Can someone please explain how this formula works?


Thanks very much!!!

 

[mikerickson]

IF($B$5:$B$14=$L5,ROW($B$5:$B$14)-ROW($B$5)+1)

is an array of row numers whose column B entry matches L5

The SMALL takes one of those and returns a value from column A

 

[Graemea]

Thank You!

 

[GORGE]

very thanks