Excel Help - How to match data from an master list w/o c&p?

[brose99]

I have an excel worksheet that pulls NCAA sports stats from a web page weekly onto Worksheet 1. Once the data is refreshed automatically, I then use the sort function to alphabetize said sports stats by team.

Worksheet 1 Example:
A B
1 TEAM OFF YARDS
2 AIR FORCE 576
3 AKRON 186
4 ALABAMA 498
5 ARIZONA 448
6 ARIZONA ST 407
.
.
.
22 CLEMSON 361
.
.
.
34 GA TECH 497
.
.
.

What I'm trying to do is take these alphabetized stats in column B of the master automated sheet (Worksheet 1) and somehow link them to the next weeks upcoming match ups format in Worksheet 2 so I don't have to cut and paste individual data.

Worksheet 2 example:

A B
1 MATCH UP OFF YARDS
2 CLEMSON ='worksheet 1'B22
3 GA TECH ='worksheet 1'B34
4
5 AIR FORCE ='worksheet 1'B2
6 ARIZONA ST. ='worksheet 1'B6
7

Since every team plays a different team each week, how do I work it so Worksheet 2 pulls the data automatically from Worksheet 1 so it matches the match up format so I don't have to individually cut and paste each value for each match up?

If I figured out how to do this, this would make my file totally automated instead of only partially automated.

Thanks in advance for your help.

 

[xlhelp]

Worksheet 2, Cell B2

=INDEX(Sheet1!B2:B34,MATCH(A2,Sheet1!A2:A34,0))

assuming last row is row 34.

A man has only two choices: He can be right or he can be happy.

 

[xlhelp]

Oops. Sorry, It should be

=INDEX(Sheet1!$B$2:$B$34,MATCH(A2,Sheet1!$A$2:$A$34,0))

(That's what happens when you test only one cell)


A man has only two choices: He can be right or he can be happy.

 

[brose99]

After some tinkering, I finally got it to work. Thanks for your help!