The formula in column H, which has become a helper column and could be moved/hidden for aesthetic purposes, has been modified to INDEX the item numbers as those don't repeat:
Formula:
=IF(H1="Desired Result",A$3,IF(COUNTIFS(H$1:H1,H1)=INDEX(B$3:B$50,MATCH(H1,A$3:A$50,0)),INDEX(A$3:A$50,MATCH(H1,A$3:A$50,0)+1),H1))
A formula now populates column I with the values:
Formula:
=INDEX(C$3:C$50,MATCH(H2,A$3:A$50,0))
The percentile formulas now reference column I (I also modified them to reference column J instead of hard coding in the percentiles, which gives the user the ability to easily change):
Formula:
=PERCENTILE.INC(I$2:I$500,J2)
Let us know if you have any questions.
Bookmarks