# Get value by multiple criteria (INDEX, MATCH, MAX), but exclude some results

1. ## Get value by multiple criteria (INDEX, MATCH, MAX), but exclude some results

Hi,
here is my problem:

I am doing a starting lineup calculator for football (soccer) teams and I need to get player's ID to his position in the team. But not only the starting XI, but the whole team. Every squad has about 28 players, so there are more people to one positions, and sometimes even 3 or 4 to the same position (e.g. 4 Right Backs or 3 Goalkeepers). And I need Excel to calculate their position - if player is rather Right Back 1 (this means he'll be in the Starting XI), Right Back 2 (he'll be on the bench) or Right Back 3/or more (he'll be in the reserves).

His ranking will be determinated:
1) by the column for Right Backs named "DR" (column "AW" in the first card of my attached workbook) > the bigger, the better

and THEN

2) by the column of his overall ability named "Cur A" (column "BN"). > the bigger, the better

3) Then (I was thinking about INDEX and MATCH functions) his "ID" (column "BQ") will be added to the specific cell (under his position and in line with his Team).

However, some players are capable of playing on more positions, but they can't be on two spots at the same time (for example, John can't be Right Back 1 and then Left Back 3) - MAX (and MIN) 1 column for every player, so I need to exclude the results of calculation, which would be the same with the ones already possessed in previous columns (e.g. Player with ID 43 can't be "Left Back 5" even if he suits best the formula, because he's already "Left Wing 2").

In the attached file, I've included 2 teams and their squads, and on the second card (where the formulas should be entered), I've manually added the needed results of the calculations for 2 Right Backs and 1 Left Back for both teams.

I hope it's not so confusing, if I missed any vital information, please let me know.

And please no macro, I don't have any experience with that.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1