Scores and Percentiles - IF or VLookup Function

Hi,

I wanted to see if there is a way to pull in the correct number from a sheet in my workbook after I put in a particular number in a cell.

Context: I want to put in a score and have the appropriate percentile pull from the percentiles sheet. The problem is that some scores can be "60" multiple times for a single player. Each 60 represents a different percentile. If I have a score of 60.30 then it should pull out the multiple of say 4 times, if indeed it comes across 4 times. If there are 4 scores of 60 and each with a different percentile, I want the closest one to 60.30, but there is no 60.30. I am manually looking at this and saying 60.25, 60.50, 60.75, and 70. Since 60.30 is close to 60.25, I would pull in the appropriate percentile.

Thank you so much for your help. I really appreciate it.

Please see percentile and formula sheet.

sampleprecentiles.xls


File: https://www.yousendit.com/download/W...dENlcEtFTmNUQw

Thanks,

Jenna

Hello All,

Thank you for taking your time out to view my post. The formula

LOOKUP(B3,'Percentiles (2)'!$B4:$CU4,'Percentiles (2)'!$B$2:$CU$2)

works, but it pulls in the wrong percentile. For example, Player W has a score of 71.23 and 71 on the percentile sheet pops up 4 times. The formula is giving me the last value with a percentile of 55. Since the score is closer to the first 71, the percentile should actually be 52. Is there a formula that will help me pull in the correct percentile if the score is in decimal format?

For example, if 71 comes 4 times, then 100/4 is 25. 71.25 is closer to the score I put in which is 71.23, therefore it would pull the proper percentile from Percentiles (2)'!$B$2:$CU$2).

I hope that makes sense.

Thank you,

Newb