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
Bookmarks