Hi! I am trying to automate a scoring process. I have a team lineup with positions and scores for each player. I need to determine the top 3 scores for the positions D and M, the top score for G and F, and finally, the top 3 remaining scores. I need 5 total scores (Goalkeeper, Defense, Midfield, Forward, Other). Below is a sample of my data table.
I currently have a second table (also below) that does a SUM function based on my manually updating the Use column (Sample F23=SUMIF(E2:E15,D23,F2:F15)). I would like to be able to either automate the population of the Use column, or update my SUM formula in the second table to determine the values. I'm only a very casual user of Excel, and this is beyond my expertise. I've tried combinations of VLOOKUP, HLOOKUP, INDEX, MATCH, and LARGE, but haven't been able to get the right combination yet. Any help someone might be able to offer is greatly appreciated.
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
I currently have a second table (also below) that does a SUM function based on my manually updating the Use column (Sample F23=SUMIF(E2:E15,D23,F2:F15)). I would like to be able to either automate the population of the Use column, or update my SUM formula in the second table to determine the values. I'm only a very casual user of Excel, and this is beyond my expertise. I've tried combinations of VLOOKUP, HLOOKUP, INDEX, MATCH, and LARGE, but haven't been able to get the right combination yet. Any help someone might be able to offer is greatly appreciated.
| Pos | Name | Column1 | Use | Score |
| G | Goalkeeper 1 | G | 6 | |
| D | Defender 1 | D | 6 | |
| D | Defender 2 | D | 7 | |
| D | Defender 3 | D | 4 | |
| D | Defender 4 | 3 | ||
| M | Midfielder 1 | M | 12 | |
| M | Midfielder 2 | M | 5 | |
| M | Midfielder 3 | O | 5 | |
| M | Midfielder 4 | M | 10 | |
| M | Midfielder 5 | 2 | ||
| M | Midfielder 6 | O | 3 | |
| F | Forward 1 | F | 6 | |
| F | Forward 2 | O | 3 | |
| F | Forward 3 | 3 | ||
| Scores | Position Name | POS | Score | |
| 1 | Goalkeeper | G | #VALUE! | 6 |
| 3 | Defenders | D | #VALUE! | 17 |
| 3 | Midfielders | M | #N/A | 27 |
| 1 | Forward | F | #VALUE! | 6 |
| 3 | Outfield Players | O | #VALUE! | 11 |
| Weekly Score | 67 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
