VLOOKUP for this problem?

  • Thread starter Thread starter Zakynthos
  • Start date Start date
Z

Zakynthos

I have an employee's name in cell A5 on a SS called Repair rotas.xls on the
Plymouth Staff tab.
The Saturday rota for this employee, called 'Saturday C', is in cell D5.

The spreadsheet on whicih I wish to input this rota is called 'Scheduling
Pots' and Amy Banks is located in cell D1588 and the cell in which I want to
input 'Saturday C' is AQ1588.

Do I use VLOOKUP to get this result and, if so, what would be the form of
this function to return the above result?

Many thanks.
 
Hi,

Move to the cell where you want the formula, and type =VLOOKUP(
click on the cell were the employees name appears, A5?. Type a comma, and
click on the sheet tab where the lookup table is, select all the data
starting with the Name column on the left and including all the column upto
the one you want to return, looks like D1:AQ2000 or something similar. Type
a comman and then the numeric position of the column you want to return, then
another comma and False)

=VLOOKUP(A5,'Scheduling Pots'!D1:AQ2000,39,FALSE)

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 
Shane,

Thanks so much for your help!

No, it didn't work - I got a #N/A in the cell.

The formula I used was something like (transferred it to OPen Office on my
Mac but created in Excel on my PC, but I think the actual form corresponds
with your example below):

=VLOOKUP('REPAIR ROTAS.XLS'!'Plymouth Staff'.$A$5;C5:AQ2000;39;FALSE)


Where have I gone wrong?
 
Hi,

I think I got the arrangement in reverse:

However, if you just wanted to do this for one cell you would enter the
followng formula in AQ1588

='REPAIR ROTAS.XLS'!'Plymouth Staff'!$A$5

Easiest way to do that is to move to AQ1588 and type = and then click on
then navigate to the Plymouth Staff sheet and click on cell A5.

If you want a formula which is more general, that will look up a name and
bring back the corresponding entry from column D, then in AQ1588 enter:

=VLOOKUP(D1588,'[REPAIR ROTAS.XLS]Plymouth Staff'!A1:D100,4,TRUE)

It is not clear if you have two workbooks open or one workbook with two
sheets, if it is 2 workbooks than the above formula.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Shane,

Thanks for this.

Didn't work, still getting #N/A in the cell.

Will summarise again:

Repair Data.xls:

Amy Banks in A5
Saturday Rota C in D5

Scheduling Pots.xls

Amy Banks in A1588
VLOOKUP FORMULA (to return a Saturday rota C) in AQ1588

Sorry if I didn't make it clearer before - your continued help is much
appreciated.
Shane Devenshire said:
Hi,

I think I got the arrangement in reverse:

However, if you just wanted to do this for one cell you would enter the
followng formula in AQ1588

='REPAIR ROTAS.XLS'!'Plymouth Staff'!$A$5

Easiest way to do that is to move to AQ1588 and type = and then click on
then navigate to the Plymouth Staff sheet and click on cell A5.

If you want a formula which is more general, that will look up a name and
bring back the corresponding entry from column D, then in AQ1588 enter:

=VLOOKUP(D1588,'[REPAIR ROTAS.XLS]Plymouth Staff'!A1:D100,4,TRUE)

It is not clear if you have two workbooks open or one workbook with two
sheets, if it is 2 workbooks than the above formula.

If this helps, please click the Yes button

Cheers,
Shane Devenshire


Zakynthos said:
Shane,

Thanks so much for your help!

No, it didn't work - I got a #N/A in the cell.

The formula I used was something like (transferred it to OPen Office on my
Mac but created in Excel on my PC, but I think the actual form corresponds
with your example below):

=VLOOKUP('REPAIR ROTAS.XLS'!'Plymouth Staff'.$A$5;C5:AQ2000;39;FALSE)


Where have I gone wrong?
 
Back
Top