Fire_Chief
Well-known Member
- Joined
- Jun 21, 2003
- Messages
- 693
- Office Version
- 365
- Platform
- Windows
I am trying to find a specific team # (1-16) in a specific region(WEST, EAST, SOUTH, MIDWEST) . I am using Absolute values with the VLOOKUP function.
If the team I want is in the South I have a VLOOKUP that goes to that column and it works fine.
However, every year the Regions change so the South might become the West column next year.
My VLOOKUP values are absolute so it would go the the place the South was last year.
I want to be able to enter (in a cell) the team # and in a second cell enter the region and have it
return the correct team.
If I want the # 1 team in the WEST my absolute value takes me to South and brings back # 1 W Carolina
IK IL
29 29
I KNOW THIS IS CONFUSING.. PLAESE ASK ME WHATEVER. I HAVE TRIED TO FIGURE THIS OUT FOR TWO DAYS
If the team I want is in the South I have a VLOOKUP that goes to that column and it works fine.
However, every year the Regions change so the South might become the West column next year.
My VLOOKUP values are absolute so it would go the the place the South was last year.
I want to be able to enter (in a cell) the team # and in a second cell enter the region and have it
return the correct team.
If I want the # 1 team in the WEST my absolute value takes me to South and brings back # 1 W Carolina
IK IL
29 29
1 | South | =IF(IL29="WEST",VLOOKUP(IK29,$IQ$8:$IR$23,2),IF(IL29="EAST",VLOOKUP(IK29,$IK$8:$IL$23,2),IF(IL29="SOUTH",VLOOKUP(IK29,$IM$8:$IN$23,2,),IF(IL29="MIDWEST",VLOOKUP(IK29,$IO$8:$IP$23,2),"TBA")))) | ||
East | West | South | Midwest | ||||
1 | Abilene Christian | 1 | Maine | 1 | W Carolina | 1 | Cal Poly |
2 | Air Force | 2 | Manhattan | 2 | W Illinois | 2 | Cal State B-field |
3 | Akron | 3 | Marist | 3 | W Kentucky | 3 | California |
4 | Alabama | 4 | Marquette | 4 | W Michigan | 4 | Campbell |
5 | Alabama A&M | 5 | Marshall | 5 | Wagner | 5 | Canisius |
6 | Alabama State | 6 | Maryland | 6 | Wake Forest | 6 | Cent Arkansas |
7 | Albany | 7 | Massachusetts | 7 | Washington | 7 | Cent Conn State |
8 | Alcorn State | 8 | McNeese State | 8 | Washington St | 8 | Cent Michigan |
9 | American U | 9 | MD-E Shore | 9 | Weber State | 9 | Charleston |
10 | Ball State | 10 | Memphis | 10 | West Virginia | 10 | Charlotte |
11 | Appalachian St | 11 | Mercer | 11 | Wichita State | 11 | Chattanooga |
12 | Arizona | 12 | Miami (FL) | 12 | William & Mary | 12 | Chicago State |
13 | Arizona State | 13 | Miami (OH) | 13 | Winthrop | 13 | Cincinnati |
14 | Ark Little Rock | 14 | Michigan | 14 | Wisconsin | 14 | Clemson |
15 | Arkansas | 15 | Michigan State | 15 | Wofford | 15 | Cleveland State |
16 | Arkansas State | 16 | Middle Tenn. | 16 | Wright State | 16 | Coast Carolina |
I KNOW THIS IS CONFUSING.. PLAESE ASK ME WHATEVER. I HAVE TRIED TO FIGURE THIS OUT FOR TWO DAYS