Return a value from a list in another worksheet

M

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.
 
M

mpanty

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

paul

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
 
M

mpanty

Thank you very much Paul! :) Worked like a charm!

One thing you forgot however :p 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! :D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can the IF function be used with values from multiple worksheets? 4
Lookup part of a cell's contents and return value of entire cell 2
using a list to return a value from another column 1
copying cell contents from one worksheet to another (re-sorted lis 2
multiple criteria from single worksheet 1
Array Pasted from One Worksheet Converts Null Values to Zeros 0
Need to find and return value in cell from multiple worksheets 0
Lookup/match value in a different worksheet, and just return a yes 4

Top