Hi, I am playing fantasy basketball and looking to use excel to maximize my daily line-up based on project points, given constraints in position. I have binary variables for position as well as whether or not a player is playing on that given day. I am using =maxifs(g/f/c,1,playing,1) and then using xlookup to retrieve the name of the player. All of that is fine and dandy.

Where I am running into issue is ensuring that once a player is selected for a position, they are no longer eligible and will then choose the second highest eligible points.

How can I specify within MAXIFS that I want to use the second highest value if the highest is already being used.





Total Formula:

=XLOOKUP(MAXIFS($C$4:$C$21,$E$4:$E$21,1,L$40:L$57,1),$C$4:$C$21,$B$4:$B$21,"")

MAXIF Formula:

MAXIFS($C$4:$C$21,$E$4:$E$21,1,L$40:L$57,1)

Note: C = player points, E = position, L = playing, B = Name