LOOKUP VALUES

Fire_Chief

Well-known Member
Joined
Jun 21, 2003
Messages
693
Office Version
  1. 365
Platform
  1. 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
1South=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"))))
EastWestSouthMidwest
1Abilene Christian1Maine1W Carolina1Cal Poly
2Air Force2Manhattan2W Illinois2Cal State B-field
3Akron3Marist3W Kentucky3California
4Alabama4Marquette4W Michigan4Campbell
5Alabama A&M5Marshall5Wagner5Canisius
6Alabama State6Maryland6Wake Forest6Cent Arkansas
7Albany7Massachusetts7Washington7Cent Conn State
8Alcorn State8McNeese State8Washington St8Cent Michigan
9American U9MD-E Shore9Weber State9Charleston
10Ball State10Memphis10West Virginia10Charlotte
11Appalachian St11Mercer11Wichita State11Chattanooga
12Arizona12Miami (FL)12William & Mary12Chicago State
13Arizona State13Miami (OH)13Winthrop13Cincinnati
14Ark Little Rock14Michigan14Wisconsin14Clemson
15Arkansas15Michigan State15Wofford15Cleveland State
16Arkansas State16Middle Tenn.16Wright State16Coast Carolina


I KNOW THIS IS CONFUSING.. PLAESE ASK ME WHATEVER. I HAVE TRIED TO FIGURE THIS OUT FOR TWO DAYS
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
How about
Fluff.xlsm
IKILIMINIOIPIQIR
6EastWestSouthMidwest
7
81Abilene Christian1Maine1W Carolina1Cal Poly
92Air Force2Manhattan2W Illinois2Cal State B-field
103Akron3Marist3W Kentucky3California
114Alabama4Marquette4W Michigan4Campbell
125Alabama A&M5Marshall5Wagner5Canisius
136Alabama State6Maryland6Wake Forest6Cent Arkansas
147Albany7Massachusetts7Washington7Cent Conn State
158Alcorn State8McNeese State8Washington St8Cent Michigan
169American U9MD-E Shore9Weber State9Charleston
1710Ball State10Memphis10West Virginia10Charlotte
1811Appalachian St11Mercer11Wichita State11Chattanooga
1912Arizona12Miami (FL)12William & Mary12Chicago State
2013Arizona State13Miami (OH)13Winthrop13Cincinnati
2114Ark Little Rock14Michigan14Wisconsin14Clemson
2215Arkansas15Michigan State15Wofford15Cleveland State
2316Arkansas State16Middle Tenn.16Wright State16Coast Carolina
24
25
26
27
28
291southW Carolina
Data
Cell Formulas
RangeFormula
IM29IM29=INDEX(IL8:IR23,XMATCH(IK29,IK8:IK23),XMATCH(IL29,IL6:IR6))
 
Upvote 0
Solution
WOW I MEAN WOW
I can't believe that works but it does.
Where can I learn HOW it works????

Thank You
Thank You
Marty
 
Last edited by a moderator:
Upvote 0
I now find that I cannot use XMATCH I have to use MATCH.
I can't get that to work
 
Upvote 0
Try this


MasterWorkbook.xlsm
BCDEFGHI
1Team #4
2RegionEast
3TeamAlabama
4
5EastWestSouthMidwest
6
71Abilene Christian1Maine1W Carolina1Cal Poly
82Air Force2Manhattan2W Illinois2Cal State B-field
93Akron3Marist3W Kentucky3California
104Alabama4Marquette4W Michigan4Campbell
115Alabama A&M5Marshall5Wagner5Canisius
126Alabama State6Maryland6Wake Forest6Cent Arkansas
137Albany7Massachusetts7Washington7Cent Conn State
148Alcorn State8McNeese State8Washington St8Cent Michigan
159American U9MD-E Shore9Weber State9Charleston
1610Ball State10Memphis10West Virginia10Charlotte
1711Appalachian St11Mercer11Wichita State11Chattanooga
1812Arizona12Miami (FL)12William & Mary12Chicago State
1913Arizona State13Miami (OH)13Winthrop13Cincinnati
2014Ark Little Rock14Michigan14Wisconsin14Clemson
2115Arkansas15Michigan State15Wofford15Cleveland State
2216Arkansas State16Middle Tenn.16Wright State16Coast Carolina
Sheet18
Cell Formulas
RangeFormula
C3C3=IFS($C$2="West",FILTER($E$7:$E$22,$D$7:$D$22=$C$1,"No Results"), $C$2="East", FILTER($C$7:$C$22,$B$7:$B$22=$C$1,"No Results"), $C$2="South", FILTER($G$7:$G$22,$F$7:$F$22=$C$1,"No Results"), $C$2="Midwest", FILTER($I$7:$I$22,$H$7:$H$22=$C$1,"No Results") )
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top