# Duplicates in an INDEX lookup

#### Graemea

##### Board Regular
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!!!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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

Thank You!

very thanks

Replies
0
Views
134
Replies
5
Views
125
Replies
16
Views
474
Replies
1
Views
361
Replies
7
Views
229

1,202,993
Messages
6,052,968
Members
444,622
Latest member
Kriszilla

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back