Hi, everyone. I've got an issue that I've been trying to solve for some time now, but I just can't quite get a formula to work how I want it to. In the worksheet linked below, I have a list of players' names and some metrics that I can use to compare them to each other. My goal is to have a formula that will work as a Nearest Neighbor Search and to have five cells at the end of each row where this formula can be used to find out each player's most comparable, 2nd most comparable, 3rd most comparable, 4th most comparable and 5th most comparable players. In columns CW to DX, I have a breakdown of how all the metrics are supposed to factor into this 'most comparable' formula. In column DY, we get a total for how each of the players compare to the first player on the list (Piper Pickett), with the player with the lowest number being the most comparable player. According to cells DZ3:ED3, the most comparable player for Piper Pickett (row 2) should be Talhah Cote (row 279), the 2nd most comparable should be Tayyab Chan, the 3rd most comparable should be Julio Bullock, the 4th most comparable should be Bradlee Parrish and the 5th most comparable should be Tobey Kelley. Instead, in cells CR2:CV2, I get Cairo Beck, Parker Greene, Talhah Cote, Rida Reyna and Rhys Osborn as the top 5 most comparable players, which are all wrong. Do any of you have any ideas on how I could fix these formulas to give me the correct results?
Link: Best Comparable Template.xlsx
Link: Best Comparable Template.xlsx