I need help indexing team names based on two criteria's
| Testing Points For.xlsm | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
| 1 | Shots | Points Win / Loss | ||||||||||||||||||||
| 2 | For | Difference | Team | Points | Difference | Against | Team | Points | Difference | Against | ||||||||||||
| 3 | VS | Team 1 | 7 | 3 | 8 | Team 1 | 8 | 3 | Team 5 | 8 | 1 | |||||||||||
| 4 | Team 2 | 4 | 3 | 4 | Team 2 | 4 | 3 | Team 4 | 8 | 1 | ||||||||||||
| 5 | VS | Team 3 | 5 | 1 | 4 | Team 3 | 4 | 1 | Team 1 | 8 | 3 | |||||||||||
| 6 | Team 4 | 6 | 1 | 8 | Team 4 | 8 | 1 | Team 6 | 4 | 1 | ||||||||||||
| 7 | VS | Team 5 | 7 | 1 | 8 | Team 5 | 8 | 1 | Team 3 | 4 | 1 | |||||||||||
| 8 | Team 6 | 6 | 1 | 4 | Team 6 | 4 | 1 | Team 2 | 4 | 3 | ||||||||||||
| 9 | ||||||||||||||||||||||
| 10 | ||||||||||||||||||||||
| 11 | All ranges F3:H8,J3:M8,O3:R8 are dynamically adjusted based on the input of Range B3:B8 & D3:D8. Currently the range O3:R8 is Index & Match based on the large value from Range K3:K6. I need the Range O3:O8 to populate based on the Largest Value in Range Points(K3:K8) and the coresponding value from the Diffence Range(L3:L8). I require the index match large formula to look up both criterias with the highest points and highest Difference value | |||||||||||||||||||||
| 12 | ||||||||||||||||||||||
| 13 | ||||||||||||||||||||||
| 14 | ||||||||||||||||||||||
| 15 | ||||||||||||||||||||||
| 16 | ||||||||||||||||||||||
| 17 | ||||||||||||||||||||||
Sheet1 | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F3,F5,F7 | F3 | =IF(D3>D4,SUM(D3-D4),IF(D4>D3,-1000,-1000)) |
| G3,G5,G7 | G3 | =IF(D3>D4,-1000,IF(D4>D3,SUM(D4-D3),-1000)) |
| H3,H5,H7 | H3 | =IF(OR(D3=-1000,D4=-1000),-1000,IF(D3>D4,8,IF(D4>D3,4,IF(D3=D4,2,-1000)))) |
| F4,F6,F8 | F4 | =IF(D4>D3,SUM(D4-D3),IF(D3>D4,-1000,-1000)) |
| G4,G6,G8 | G4 | =IF(D4>D3,-1000,IF(D3>D4,SUM(D3-D4),-1000)) |
| H4,H6,H8 | H4 | =IF(OR(D3=-1000,D4=-1000),-1000,IF(D4>D3,8,IF(D3>D4,4,IF(D3=D4,2,-1000)))) |
| J3:J8 | J3 | =IF(B3="","",B3) |
| K3:K8 | K3 | =IF(J3="","",VLOOKUP(J3,$B$3:$H$8,7,0)) |
| L3:L8 | L3 | =VLOOKUP(J3,$B$3:$H$8,5,0) |
| M3:M8 | M3 | =VLOOKUP(J3,$B$3:$H$8,6,0) |
| O3:O8 | O3 | =INDEX($J$3:$J$8,MATCH(LARGE($K$3:$K$8+ROW($K$3:$K$8)/10^10,ROWS($O$3:O3)),$K$3:$K$8+ROW($K$3:$K$8)/10^10,0)) |
| P3:P8 | P3 | =VLOOKUP(O3,$J$3:$M$8,2,0) |
| Q3:Q8 | Q3 | =VLOOKUP(O3,$J$3:$M$8,3,0) |
| R3:R8 | R3 | =VLOOKUP(O3,$J$3:$M$8,4,0) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| O3:R8 | Cell Value | =-1000 | text | NO |
| O3:R7 | Expression | =$P3=-1000 | text | NO |
| O8:R8 | Expression | =$P8=-1000 | text | NO |
| J3:M8 | Cell Value | =-1000 | text | NO |
| D3:H8 | Cell Value | =-1000 | text | NO |
