Index/Match for multiple criteria duplicate rank

Hi,

I've come across various threads that discuss index/match issues for duplicate records, although I'm having trouble applying it to where I have multiple criteria.


This is from the actual file.

Please Login or Register  to view this content.

This is from the sample I have uploaded. The sample file, treat the output tab as something that cannot be modified. The Master tab can be modified.

Please Login or Register  to view this content.

The workbook "Output File - October2015.xlsx" has the duplicate value in column "R" which the file cannot be sorted as there are multiple rankings occurring on the sheet. Note: I will not be able to make changes to the workbook I initially referenced as "output file..."

The sheet that I am updating looks like this for example. The sample file attached will show the exact issue in Row 4.Attachment 432323
Rank (fixed) Index/match formula field
1 found rank 1 data
2 found rank 2 data (but dupe exists)
3 returns N/A as no rank 3 exists on output file but there is a second rank 2 down the list
4 found rank 4 data


What I am intending to occur is find the next rank 2 data, the other roadblock is the formula is looking for rank 3 altogether.

I appreciate the advice.

Thanks,
Lazo