IFS, VLookup & Index Match Values Method?

[wigginsda]

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

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

 

[wigginsda]

Using Excel- Version 2208 Build 16.0.15601.20148) 64-bit