IF formula to assign players to teams based on age & weight

Hello,
My level of knowledge of excel is limited so please bare with me.
I have about 2,000 players that I need to assign to their playing level (flag, widget, pw teal etc).
I'm trying to have a formula that will place the player based on his age & weight to the correct level. In the end, I'm looking for the formula to come back with the players level.
The first criteria used to determine which team they are placed on is their "age".
I have gotten that far using IF formula.
Then I get stuck......the 2nd criteria that trumps age is "weight"

Below is the league reference chart that I am working from
League Reference Chart
Age / Wt / Level
6 / / Flag (flag wt is unlimited)
7 / / Flag
8 / 80 / Widget
9 / 80 / Widget
10 / 90 / PW Teal
11 / 100 / Pw Black
12 / 110 / LW Teal
13-14 / 120 / LW Black
13-14 / 170 / Varsity

Example 1: Johnny is age 11 and 98lbs = PW Black
Based on the players age and weight he is placed on the PW Black team


Example 2: Jimmy is age 11 and 130lbs = Varsity
Based on the players age alone he qualifies for the PW Black team, however, because his weight exceeds the max allowed for that level (100lbs) he then gets bumped up to the level where is weight does not exceed the limit. In this example it would be Varisty.


I have attached a sample worksheet of what I have so far with the IF formula. I could be way off base and maybe their is an easier formula.
I could not figure out how to check the players weight in my formula....so it only contains the age.

Thanks in advance.

Put this formula in C2:

=IF(A2<8,"Flag",INDEX(C$18:C$24,MAX(MATCH(A2,A$18:A$24),MATCH(B2,B$18:B$24))))

then copy down.

Hope this helps.

Pete

Pete,
This is outstanding!
Is there a way to account for players who's weights might be less than 80lbs?
Right now if I enter a weight below 80lbs #N/A is displayed.

For example
Johnny is 11 years old and weighs 45lbs - his level would remain at PW Black
Can the formula be adjusted to account for this?
(FYI...Older players can not move down regardless of weight)

Thanks.....this is a huge step in the right direction.

You need to put zero in cell B17, then you can amend the formula to this:

Formula:

Please Login or Register  to view this content.

so that the ranges start at row 17 instead of 18, then copy down, as in the attached file.

Hope this helps.

Pete

Pete...
Thank you so much, you have no idea how much you helped!
Have a great weekend.
Rob