Lookup + Duplicate Values

Hello all,

My attempts at this problem are getting me nowhere (http://www.excelforum.com/excel-work...24#post2817524).

Here's the situation:

- I have a dataset that grows each month (refreshed monthly)
- dataset is 50 names (labels); each name has a value
- there may be no to many duplicate values within a month (I'm not worried about duplicates between months)

I need to:

- take the top 15 values and lookup the corresponding names

The problem:

I can sort the top 15 values and lookup the corresponding name. When there are duplicate values however, I get the same name duplicated: the lookup matches only the first instance of the duplicated value and returns the name for that instance. I'm trying to return the name for the second (and nth) instance of the duplicated value.

Solution constraints:

- I need to avoid pivot tables and macros; this workbook will auto-update with no manual intervention (set and forget) and the workbook can't set off security warnings or trip spam filters (email distribution)
- users are able to switch the selected month via drop-down box (in order to see historical data)
- the labels may change when the data refreshes; the top 25 are likely to remain constant but their order will change (sorting is on the metrics, not the label); in other words, the actual value of label 5 one month may be label 7 the next, and label 10 the month after

Worksheet with sample data and the lookups I've got so far is attached.

Any help is appreciated!

Is the order of the returned names relative to the duplicates critical?

The following formula array confirmed in D3, then filled down will give the correct results, but the order for the repeated value of 496 would be Label 49, Label 5, Label 4, the reverse of your example.

=INDEX(labels,INT(MOD(LARGE(unorderedMetrics+(ROW(unorderedMetrics)/100)+(COLUMN(unorderedMetrics)/10000),ROWS(D$3:D3)),1)*100)-6)

I've not studied your problem in detail, but you need to include a tie-break to be able to separate out the duplicates. A common way is to add or subtract a small number which is dependent on the position of the value in the unsorted range, such as using a COUNTIF divided by 10000, or just the row where it occurs divided by 1000000. These small fractions will not show if the cells are formatted as numbers with zero decimal places. Using the latter approach you can find the row quite easily by just looking at the fractional part and multiplying by 1000000 (or whatever) and use that instead of MATCH.

Hope this helps.

Pete

Hello all,

Sorry for the delay getting back to this, was occupied with other things. I wound up using Haseeb's suggestion. Jason - no, order isn't important in my case, where there are duplicates, the lookup can return the labels in any order.

I was hoping to keep everything in named ranges or named formulas but it seems that solution is going to be not possible. (I've created a lookup tab instead.)

I'm going to mark this solved but if you'd like to keep tossing around ideas, I'd be interested in seeing a solution that will work without dumping the array of sorted values into a range.

Thanks all, very very much appreciated!
Keith