IFS, VLookup & Index Match Values Method?

wigginsda

New Member
Joined
May 15, 2010
Messages
5
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
ABCDEFGHIJKL
1Total Team PointsPosition 1 RankPosition 2 RankPosition 3 RankPosition 4 Rank3 MatchesValue To Match
2Group 17.0412137.047.04
3Group 26.9823117.03
4Group 36.8531227.06
5Group 47.061312
6Group 56.813132Determining the 2nd string of numbers
7Group 66.9423211st column check2nd column check3rd column check
8Group 77.0312327.066.946.81
96.986.85
10
11Total Team PointsPosition 1 PointsPosition 2 PointsPosition 3 PointsPosition 4 PointsIFS#ERROR!
12Group 17.041.911.831.761.54
13Group 26.981.771.781.761.67Best Matches
14Group 36.851.631.891.721.611st Pick1213
15Group 47.061.911.781.761.612nd Pick2321
16Group 56.811.631.891.681.613rd Pick3132
17Group 66.941.771.781.721.67
18Group 77.031.911.831.681.61
19
20
21Class 1 Rank and Point ValuePosition 2 Rank and Point ValuePosition 3 Rank and Point ValuePosition/ Rank and Point Value
2211.9111.8911.7611.67
2321.7721.8321.7221.61
2431.6331.7831.6831.54
Sheet1
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top