Array formula for TOP 10 - how to deal with duplicate values

Hello all,

I am using the below formula to give me the Top 10 project numbers based on various criteria, see attached spreadsheet.

{=INDEX('Current Month'!$A:$AN,MATCH(LARGE(IF('Current Month'!$AM:$AM<>"X",IF('Current Month'!$F:$F=$A$29,IF('Current Month'!$AJ:$AJ="Increase",'Current Month'!$AI:$AI))),$A39),'Current Month'!$AI:$AI,0),1)}

It is working fine, except for when there are 2 equal values in the source data which have different criteria.

The problem means the formula returns an incorrect result, the value in the row to match is 200k, and there are 2 rows of 200k in the source data, and the wrong one is first in the list, so this is the one being returned.

How do I get around this? My guess is to somehow add a very small number to each value, but I'm not sure how to do this.

Also, I intend to amend the formula to use dynamic ranges in order to reduce memory usage, and will do this when I have sorted this issue out.

I hope this is clear, thanks for any help!

Rich

the formula you have posted does not match the sheet names or cell reference's in that workbook

I have updated the attachment, and it should now match

Thanks

Hi,

One way is to repeat the criteria for the lookup_array as well:

=INDEX('Current Month'!$A:$AN,MATCH(LARGE(IF('Current Month'!$AM:$AM<>"X",IF('Current Month'!$F:$F=$A$29,IF('Current Month'!$AJ:$AJ="Increase",'Current Month'!$AI:$AI))),$A39),IF('Current Month'!$AM:$AM<>"X",IF('Current Month'!$F:$F=$A$29,IF('Current Month'!$AJ:$AJ="Increase",'Current Month'!$AI:$AI))),0),1)

Regards

I would never have thought of that, thank you very much!

You're welcome.