IFS Lookup & Index Match Values Method?

Excel- Version 2208 Build 16.0.15601.20148 64-bit



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

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.