Adjacent cell reference - avoiding duplicates

I'm trying to reference an adjacent cell, but in some cases there may be duplicate, triplicates or more of that number so Vlookup won't work. I've attached the spread sheet and highlighted 2 example cells that I'm having trouble with the output b/c of duplication issue. I've included what the output should be and a couple of formulas I've tried. The data is stored on sheet 2, and the formulas are in sheet 1.

Column E on sheet one is pulling the top 10 numbers from sheet 2, colunm N. I want to display the adjacent cell in sheet 1 column F. Can anyone assist without doing VBA or nesting several IF stmts.

Once I can get the adjacent cells I can do IF/AND stmt to pull in the other data or repurpose the formula in question.

Thanks in advance

Are you able to sort the sheet by column P descending so that you get the largest relevant value in column P for the value in column N?

Originally Posted by mrice

Are you able to sort the sheet by column P descending so that you get the largest relevant value in column P for the value in column N?

I'd like to avoid doing any manual sorting. I thought there was an easy way to get the adjacent cell by nesting a couple of of the following: IF/AND/OR/LARGE/CELL/ROW/INDEX/MATCH.