Lookup Values based on criteria from two sheets

In my attached worksheet, I would like the green and yellow boxes at the top of my sheet to fill in with numbers supplied by data on that current sheet as well as one other sheet in my worksheet.

I have a list of 20 teams with a grid of players (N6:AD25) for each team, along with a team budget (AE6:AE25) and a team max bid (AF6:AF25). I would like to have the three boxes up top that are blank look up whatever team I select in cell I3 of the Draft Board sheet to then tell the rest of the sheet what to do based on which team is selected.

For example, the Green Box cell U2, should say $150 because the remaining budget listed in column AE of the Draft Board sheet for Team 1 is $150. We know to look for team 1's budget because that is the team selected in cell I3.

For the second Green Box cell AF2, should then say $27.50 because that is the average amount of money spent on the players selected by team 1. We know this because team 1 has 4 players picked in cells N6:AD6 and $110 of the $260 starting budget has been spent. So $110/4 = $27.50. The starting budget is listed in cell C2 of the Settings tab.

For the final Yellow Box cell AP2, it should say $126 because that is the max bid listed in cell AF6 for Team 1.

Use column AR To know which team is which for cells N6:AF25.

Please let me know if you have any questions, thanks!

Originally Posted by FDibbins

1st, an observation...
please oh please try and avoid using merged cells like that, they cause havoc with formulas!! In this case, they are completely unnecessary, just widen your rows and colulms where needed.

OK
U2=INDEX($AE$6:$AE$25,MATCH(--RIGHT(I3,2),$M$6:$M$25,0))
AF2=(Settings!$C$2-$U$2)/COUNTIF(OFFSET($M$6,MATCH(--RIGHT(I3,2),$M$6:$M$25,0)-1,1,1,17),">0")
AP2=INDEX($AF$6:$AF$25,MATCH(--RIGHT(I3,2),$M$6:$M$25,0))

Thanks for the advice! Works great

Originally Posted by FDibbins

1st, an observation...
please oh please try and avoid using merged cells like that, they cause havoc with formulas!! In this case, they are completely unnecessary, just widen your rows and colulms where needed.

OK
U2=INDEX($AE$6:$AE$25,MATCH(--RIGHT(I3,2),$M$6:$M$25,0))
AF2=(Settings!$C$2-$U$2)/COUNTIF(OFFSET($M$6,MATCH(--RIGHT(I3,2),$M$6:$M$25,0)-1,1,1,17),">0")
AP2=INDEX($AF$6:$AF$25,MATCH(--RIGHT(I3,2),$M$6:$M$25,0))

One question, if I wanted to make the Average Spent by Player formula blank until a player is put on that team instead of it showing -260 how would I do that?

Originally Posted by FDibbins

1st, an observation...
please oh please try and avoid using merged cells like that, they cause havoc with formulas!! In this case, they are completely unnecessary, just widen your rows and colulms where needed.

OK
U2=INDEX($AE$6:$AE$25,MATCH(--RIGHT(I3,2),$M$6:$M$25,0))
AF2=(Settings!$C$2-$U$2)/COUNTIF(OFFSET($M$6,MATCH(--RIGHT(I3,2),$M$6:$M$25,0)-1,1,1,17),">0")
AP2=INDEX($AF$6:$AF$25,MATCH(--RIGHT(I3,2),$M$6:$M$25,0))

Disregard that last post it says zero which is fine, appreciate your help!