# Need help finding closest comparable player in a database

#### alh20

##### New Member
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?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
the results of the formulas in columns CR:CV is completely different of the result in DY, factor 100
So the method isn't exact the same

the results of the formulas in columns CR:CV is completely different of the result in DY, factor 100
So the method isn't exact the same
Yeah, the results in columns CW:DY are just to show how I want each metric on the worksheet to factor into the formula, because some metrics (column BC) are more important than others (column AN). The results in columns CR:CV, on the other hand, are just how far I can take these formulas before I just completely mess everything up.

the formula used to give a weight at every metric, is that easy to explain ?
When I see that huge formula error seeking is almost impossible.
Can it be done with macros, VBA (UDF) ?

the formula used to give a weight at every metric, is that easy to explain ?
When I see that huge formula error seeking is almost impossible.
Can it be done with macros, VBA (UDF) ?
Yeah, so what I'm ultimately aiming for is a formula that starts out by checking the heights and weights of each player and disregarding any players who are 2 inches taller/shorter than or 10 pounds heavier/lighter than the original player they're being compared to. Then I'm trying to combine into the remainder of this larger formula all the smaller, individual formulas (with weights) found from columns I:CC and find out which row would, in the end, have the lowest sum based on all those smaller formulas (as seen in totals found in column DY). Finally, based on that lowest sum, I want it to spit out the player's name that matches that lowest number. In this case of the worksheet I linked, the lowest (non-zero) number in column DY is 69.0071 (row 279) and the player associated with that lowest number is Talhah Cote - so Cote would be Piper Pickett's (row 2) closest comparison.

If you need a better explanation for any part of this post, just let me know and I'll try to clarify as best as I can. Also, I don't really have a particular preference for how it's done. Whether it's through Excel formulas or VBA, I just want it to work.

how do i add an excel-file (650kB) here on the site ?

how do i add an excel-file (650kB) here on the site ?
You can't, however you can use the Xl2BB add-in to post data to the site, along with any formulae & if your posting code, just post it to the site using code tags

testing
Best Comparable Template .xlsb
CPCQCRCSCTCUCVCWCX
1Metric 54Metric 55Best ComparableBest Comparable #2Best Comparable #3Best Comparable #4Best Comparable #5FG
21,1N/ATalhah CoteTayyab ChanJulio BullockBradlee ParrishTobey Kelly#N/B#N/B
31,0N/ASacha BruceJordyn CampbellJarod ShepherdTorin GriffithKester Juarez02,4
41,2N/AConrad HoustonHarlan LinElias HooperMitchell StarkDru Crawford#N/B#N/B
51,1N/AArturo McmillanCordell MosesTony GillespieJaya WatsonGurveer Gough#N/B#N/B
63,1N/AJorgie KaurDeja GoodNicola ColemanHaaris DawsonMoses Aguirre#N/B0
71,1N/ASacha BruceBrody OrozcoIrfan SinghKaiden LiJarod Shepherd06,4
83,3N/ACamryn WattsGage BlairLorcan HartShawn JamesKory Muir07,2
94,3N/ASpencer WagnerEoin WarnerVivaan SheldonEesa HarwoodHussein Dale#N/B#N/B
101,0N/ACem McintoshJodi GilmoreAmani MacgregorMatthew BentonVincent Dalby#N/B#N/B
EDGE
Cell Formulas
RangeFormula
CW2:CW10CW2=IF(OR((ABS(EDGE!\$F\$2:\$F\$643-INDEX(EDGE!\$F\$2:\$F\$643,Pointer))*4)>300,(ABS(EDGE!\$F\$2:\$F\$643-INDEX(EDGE!\$F\$2:\$F\$643,Pointer))*4)>=8),#N/A,ABS(EDGE!\$F\$2:\$F\$643-INDEX(EDGE!\$F\$2:\$F643,Pointer))*4)
CX2:CX10CX2=IF((ABS(EDGE!\$G\$2:\$G\$643-INDEX(EDGE!\$G\$2:\$G\$643,Pointer))*0.8)>100,#N/A,IF((ABS(EDGE!\$G\$2:\$G\$643-INDEX(EDGE!\$G\$2:\$G\$643,Pointer))<=10),(ABS(EDGE!\$G\$2:\$G\$643-INDEX(EDGE!\$G\$2:\$G\$643,Pointer))*0.8),#N/A))
Named Ranges
NameRefers ToCells
Pointer=EDGE!\$EA\$1CW2:CX10

Cell Formulas
RangeFormula
EB2:EH2EB2=AGGREGATE(15,6,\$DY\$2:\$DY\$638,EB1)
EA3EA3=INDEX(\$B\$2:\$B\$638,Pointer)
EB3:EH3EB3=INDEX(\$B\$2:\$B\$638,MATCH(EB2,\$DY\$2:\$DY\$638,0))
Named Ranges
NameRefers ToCells
Name=EDGE!\$B\$2:\$B\$643EA3:EH3
Pointer=EDGE!\$EA\$1EA3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
EA3:EB3Expression=\$EA\$3<>\$EB\$3textNO
EB2Expression=\$EB\$2<>0textNO

Replies
2
Views
2K

1,196,073
Messages
6,013,267
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back