What kind of formula would transfer top 5 list to a different sheet based on player's name

**ak20** · 05-01-2018, 08:27 PM

I recently posted on this site seeing if anyone could help me with a problem I had with my database of NFL players (luckily, JeteMc was able to help me out with that). In this workbook, there are two sheets: one filled with different metrics for players (i.e. height, weight, 40 yard dash and college production) and another that is used to generate a list of the 5 closest comparisons for each player. Now that I can get a top 5 list, I'm trying to find out how to get that top 5 list to transfer over from that sheet to the sheet filled with the data (titled 'CBs'). For instance, cells AJ2, AK2, AL2, AM2 and AN2 would gather Sherman's 1st, 2nd, 3rd, 4th and 5th closest comparisons from the list on the sheet titled 'CB Comparison'. Looking forward to seeing any suggestions!

**JeteMc** · 05-02-2018, 10:33 AM

The easiest way that I could think of is to do this is to make another table in columns AO:TC (which could be moved and/or hidden for aesthetic purposes) with the CB's names as the column headers and populated by an inverse (I think that you'll understand when you look at the formula in AO2) of the formula used to populate column A on the CB comparison sheet.
Columns AJ:AN are populated using:

Formula:

Please Login or Register  to view this content.

Note: to keep the size of the attached file relatively low (for uploading/downloading) I only copied the formulas down as far as row 20.
Let us know if you have any questions.