LOOKUP VALUES

[Fire_Chief]

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

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

 

[Fluff]

How about

Fluff.xlsm
	IK	IL	IM	IN	IO	IP	IQ	IR
6		East		West		South		Midwest
7
8	1	Abilene Christian	1	Maine	1	W Carolina	1	Cal Poly
9	2	Air Force	2	Manhattan	2	W Illinois	2	Cal State B-field
10	3	Akron	3	Marist	3	W Kentucky	3	California
11	4	Alabama	4	Marquette	4	W Michigan	4	Campbell
12	5	Alabama A&M	5	Marshall	5	Wagner	5	Canisius
13	6	Alabama State	6	Maryland	6	Wake Forest	6	Cent Arkansas
14	7	Albany	7	Massachusetts	7	Washington	7	Cent Conn State
15	8	Alcorn State	8	McNeese State	8	Washington St	8	Cent Michigan
16	9	American U	9	MD-E Shore	9	Weber State	9	Charleston
17	10	Ball State	10	Memphis	10	West Virginia	10	Charlotte
18	11	Appalachian St	11	Mercer	11	Wichita State	11	Chattanooga
19	12	Arizona	12	Miami (FL)	12	William & Mary	12	Chicago State
20	13	Arizona State	13	Miami (OH)	13	Winthrop	13	Cincinnati
21	14	Ark Little Rock	14	Michigan	14	Wisconsin	14	Clemson
22	15	Arkansas	15	Michigan State	15	Wofford	15	Cleveland State
23	16	Arkansas State	16	Middle Tenn.	16	Wright State	16	Coast Carolina
24
25
26
27
28
29	1	south	W Carolina
Data

Cell Formulas
Range	Formula
IM29	IM29	=INDEX(IL8:IR23,XMATCH(IK29,IK8:IK23),XMATCH(IL29,IL6:IR6))

 

[Fire_Chief]

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

 

[Fluff]

You're welcome & thanks for the feedback.

To find out how it works have a look here How to use INDEX and MATCH

 

[Fire_Chief]

I now find that I cannot use XMATCH I have to use MATCH.
I can't get that to work

 

[skillilea]

Try this

MasterWorkbook.xlsm
	B	C	D	E	F	G	H	I
1	Team #	4
2	Region	East
3	Team	Alabama
4
5	East		West		South		Midwest
6
7	1	Abilene Christian	1	Maine	1	W Carolina	1	Cal Poly
8	2	Air Force	2	Manhattan	2	W Illinois	2	Cal State B-field
9	3	Akron	3	Marist	3	W Kentucky	3	California
10	4	Alabama	4	Marquette	4	W Michigan	4	Campbell
11	5	Alabama A&M	5	Marshall	5	Wagner	5	Canisius
12	6	Alabama State	6	Maryland	6	Wake Forest	6	Cent Arkansas
13	7	Albany	7	Massachusetts	7	Washington	7	Cent Conn State
14	8	Alcorn State	8	McNeese State	8	Washington St	8	Cent Michigan
15	9	American U	9	MD-E Shore	9	Weber State	9	Charleston
16	10	Ball State	10	Memphis	10	West Virginia	10	Charlotte
17	11	Appalachian St	11	Mercer	11	Wichita State	11	Chattanooga
18	12	Arizona	12	Miami (FL)	12	William & Mary	12	Chicago State
19	13	Arizona State	13	Miami (OH)	13	Winthrop	13	Cincinnati
20	14	Ark Little Rock	14	Michigan	14	Wisconsin	14	Clemson
21	15	Arkansas	15	Michigan State	15	Wofford	15	Cleveland State
22	16	Arkansas State	16	Middle Tenn.	16	Wright State	16	Coast Carolina
Sheet18

Cell Formulas
Range	Formula
C3	C3	=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") )

 

[Fluff]

I now find that I cannot use XMATCH I have to use MATCH.

In that case use

=INDEX(IL8:IR23,MATCH(IK29,IK8:IK23,0),MATCH(IL29,IL6:IR6,0))