Seven groups of numbers are being compared. Based on the total points, I would like to determine the three most optimal group configurations (optimal meaning the highest and most equal values). These three groups will have the highest point value, but with the restriction that there are only 12 possible "players". The same number/player cannot appear in more than one column.
For example, reading 1|2|3|2 represents the #1 ranked player in class 1, the #2 ranked player in class 2, the #3 ranked player from class 3, and the #2 ranked player in class 4.
After the first column of numbers is checked for duplicates, the second, third, and fourth columns will be checked before the final set of three numbers is determined.
Currently, I'm nesting vlookup, index, etc.
=if(vlookup(I2,B2:F8,2,False)=vlookup(I3,B2:F8,2,False),If(vlookup(I2,B2:F8,2,False)=vlookup(I3,B2:F8,2,False),INDEX($B$2:$B$8,MATCH(SMALL(ABS(J2-$B$2:$B$8),4),ABS(J2-$B$2:$B$8),0)),INDEX($B$2:$B$8,MATCH(SMALL(ABS(J2-$B$2:$B$8),2),ABS(J2-$B$2:$B$8),0))))
The original dataset on which this is based has nearly 400 groups of numbers that compares 6 ranks of players among the 4 categories. Nesting that many statements doesn't seem like the most efficient method. Do I have another choice?
IFS seems like a possibility but I cant seem to get the syntax when I put Vlookup and Index together.
=INDEX($B$2:$B$8,MATCH(MIN(ABS($B$2:$B$8-J1)),ABS($B$2:$B$8-J1),0)),IFS(J14=1,"No",J14=2,"yes2",J14=3,"yes3")
Hopefully, this makes sense, and I would be happy to answer any questions you might have. Thank you for any insight.
Thank you,
David
For example, reading 1|2|3|2 represents the #1 ranked player in class 1, the #2 ranked player in class 2, the #3 ranked player from class 3, and the #2 ranked player in class 4.
After the first column of numbers is checked for duplicates, the second, third, and fourth columns will be checked before the final set of three numbers is determined.
Currently, I'm nesting vlookup, index, etc.
=if(vlookup(I2,B2:F8,2,False)=vlookup(I3,B2:F8,2,False),If(vlookup(I2,B2:F8,2,False)=vlookup(I3,B2:F8,2,False),INDEX($B$2:$B$8,MATCH(SMALL(ABS(J2-$B$2:$B$8),4),ABS(J2-$B$2:$B$8),0)),INDEX($B$2:$B$8,MATCH(SMALL(ABS(J2-$B$2:$B$8),2),ABS(J2-$B$2:$B$8),0))))
The original dataset on which this is based has nearly 400 groups of numbers that compares 6 ranks of players among the 4 categories. Nesting that many statements doesn't seem like the most efficient method. Do I have another choice?
IFS seems like a possibility but I cant seem to get the syntax when I put Vlookup and Index together.
=INDEX($B$2:$B$8,MATCH(MIN(ABS($B$2:$B$8-J1)),ABS($B$2:$B$8-J1),0)),IFS(J14=1,"No",J14=2,"yes2",J14=3,"yes3")
Hopefully, this makes sense, and I would be happy to answer any questions you might have. Thank you for any insight.
Thank you,
David
IFS_VLookup_Index_Match_Values_Method.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Total Team Points | Position 1 Rank | Position 2 Rank | Position 3 Rank | Position 4 Rank | 3 Matches | Value To Match | |||||||
2 | Group 1 | 7.04 | 1 | 2 | 1 | 3 | 7.04 | 7.04 | ||||||
3 | Group 2 | 6.98 | 2 | 3 | 1 | 1 | 7.03 | |||||||
4 | Group 3 | 6.85 | 3 | 1 | 2 | 2 | 7.06 | |||||||
5 | Group 4 | 7.06 | 1 | 3 | 1 | 2 | ||||||||
6 | Group 5 | 6.81 | 3 | 1 | 3 | 2 | Determining the 2nd string of numbers | |||||||
7 | Group 6 | 6.94 | 2 | 3 | 2 | 1 | 1st column check | 2nd column check | 3rd column check | |||||
8 | Group 7 | 7.03 | 1 | 2 | 3 | 2 | 7.06 | 6.94 | 6.81 | |||||
9 | 6.98 | 6.85 | ||||||||||||
10 | ||||||||||||||
11 | Total Team Points | Position 1 Points | Position 2 Points | Position 3 Points | Position 4 Points | IFS | #ERROR! | |||||||
12 | Group 1 | 7.04 | 1.91 | 1.83 | 1.76 | 1.54 | ||||||||
13 | Group 2 | 6.98 | 1.77 | 1.78 | 1.76 | 1.67 | Best Matches | |||||||
14 | Group 3 | 6.85 | 1.63 | 1.89 | 1.72 | 1.61 | 1st Pick | 1 | 2 | 1 | 3 | |||
15 | Group 4 | 7.06 | 1.91 | 1.78 | 1.76 | 1.61 | 2nd Pick | 2 | 3 | 2 | 1 | |||
16 | Group 5 | 6.81 | 1.63 | 1.89 | 1.68 | 1.61 | 3rd Pick | 3 | 1 | 3 | 2 | |||
17 | Group 6 | 6.94 | 1.77 | 1.78 | 1.72 | 1.67 | ||||||||
18 | Group 7 | 7.03 | 1.91 | 1.83 | 1.68 | 1.61 | ||||||||
19 | ||||||||||||||
20 | ||||||||||||||
21 | Class 1 Rank and Point Value | Position 2 Rank and Point Value | Position 3 Rank and Point Value | Position/ Rank and Point Value | ||||||||||
22 | 1 | 1.91 | 1 | 1.89 | 1 | 1.76 | 1 | 1.67 | ||||||
23 | 2 | 1.77 | 2 | 1.83 | 2 | 1.72 | 2 | 1.61 | ||||||
24 | 3 | 1.63 | 3 | 1.78 | 3 | 1.68 | 3 | 1.54 | ||||||
Sheet1 |