Return a value from a list in another worksheet

[mpanty]

Hello,

I manage a sports league, and one of my most common tasks is to make
the season schedule at the beginning of the year, where each time has
to play another, at different times of the week. Each team has sent me
a set of availability restrictions, days&times of the week that they
are unable to play. Thus, when I make my schedule, I have to take these
restrictions into account.

I have one separate worksheet for the game *schedule*, and another for
the *restrictions*. Thus in the worksheet "schedule" I have the home
team under column A, playing versus the away team on column B. In the
worksheet "restrictions", I have the team name under column A, and
their restrictions under column B.

e.g.

_\"Schedule\"_worksheet_
-Column A / Column B-
Footsies / MISN Impossible
Money Shot / Rejects
Grrr / McGrads I
Pink Seagulls / Mailmen FC
etc.

_\"Restrictions\"_worksheet_
-Column A / Column B-
Footsies / no Jan 9,12,16 no Feb 6-8
Money Shot / No Jan 9, No Thursdays
Grrr / no Jan 16
etc.

My question: is there a way in Excel to set up columns C & D in the
worksheet "schedule" such that it returns the restrictions I have set
up in the worksheet "restrictions"? (column C for the home team, and
column D for the away team). I basically need a function that goes
through the list in A, matches the team name in the restrictions
worksheet, and then returns the restriction "value" (which is in fact,
text) in the "schedule" worksheet.

I tried the "IF" function, but it doesn't work. Any help would be
appreciated.

 

[mpanty]

No clues? Seems pretty straightforward in theory... I just don't have
sufficient Excel knowledge to know of the right formula...

 

[Guest]

use vlookup to look for the team name in the other workshheet and return the
restriction in cols c for col a and d for colb
the lookup formula say in col c1 of sheet "schedule" looks like this
=vlookup(A1,restrictions!A1:B7,false)
for this to work the team names(spelling wise) have to be identical in both
sheets

 

[Guest]

what no response???

 

[mpanty]

Thank you very much Paul! Worked like a charm!

One thing you forgot however was to specify the "Col_index_num". The
function is in fact with the format
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) where
Col_index_num is the column number in table_array from which the
matching value must be returned. A col_index_num of 1 returns the value
in the first column in table_array; a col_index_num of 2 returns the
value in the second column in table_array, and so on. In my case, I
needed "2".

But thanks again!

 

[Guest]

oops youre right,but glad you sorted it out....