multiple vlookup

[[email protected]]

I have a different workbooks, the one workbook takes the output from
out employee time logging system and outputs in a format like this

All.xls
Name time in time out
ABC Robert L Jones 09:00 17:30
SDDF James Smith 09:05 17:20
etc...

The other workbooks are the managers ones that contain just the info
they need.

Manager1.xls
And then in the different manager's excel workbook we have it like
this:
Name time in time out
Bob Jones 09:00 17:30

Manager2.xls
Name time in time out
Jim Smith 09:00 17:30

I created a sheet "matches" that matches the output from the system to
the manager's name like this:
System output Manager workbook
ABC Robert L Jones Bob Jones
SDDF James Smith Jim Smith

I would like to link the manager's work book to the all.xls workbook so
that I can automatically pull out the time in and time out stats from
the all.xls system output. I could do this using vlookup if the names
in the manager's workbook were the same in the all.xls but now have to
somehow do two lookups first in the "match" sheet and then to pull up
from the all.xls worksheet.

How would I do a double vlookup.

A single lookup in the manager's workbook without referencing the name
matches would look something like this:
VLOOKUP($A2,'[All.xls]29'!$A$2:$C$84,2,FALSE) for the time in
VLOOKUP($A2,'[All.xls]29'!$A$2:$C$84,2,FALSE) for the time out
Where 29 is the name of the sheet (representing todays date)

[damorrison]

Is there an employee number for each staff??

[daddylonglegs]

You should just be able to replace the $A2 in this formula

VLOOKUP($A2,'[All.xls]29'!$A$2:$C$84,2,FALSE)

with your first VLOOKUP, e.g. something like

VLOOKUP(VLOOKUP($A2,namematchtable,2,0),'[All.xls]29'!$A$2:$C$84,2,FALSE)

[Philippe L. Balmanno]

<[email protected]> wrote in message
news:[email protected]...
>I have a different workbooks, the one workbook takes the output from
> out employee time logging system and outputs in a format like this
>
> All.xls
> Name time in time out
> ABC Robert L Jones 09:00 17:30
> SDDF James Smith 09:05 17:20
> etc...
>
> The other workbooks are the managers ones that contain just the info
> they need.
>
> Manager1.xls
> And then in the different manager's excel workbook we have it like
> this:
> Name time in time out
> Bob Jones 09:00 17:30
>
> Manager2.xls
> Name time in time out
> Jim Smith 09:00 17:30
>
> I created a sheet "matches" that matches the output from the system to
> the manager's name like this:
> System output Manager workbook
> ABC Robert L Jones Bob Jones
> SDDF James Smith Jim Smith
>
> I would like to link the manager's work book to the all.xls workbook so
> that I can automatically pull out the time in and time out stats from
> the all.xls system output. I could do this using vlookup if the names
> in the manager's workbook were the same in the all.xls but now have to
> somehow do two lookups first in the "match" sheet and then to pull up
> from the all.xls worksheet.
>
> How would I do a double vlookup.
>
> A single lookup in the manager's workbook without referencing the name
> matches would look something like this:
> VLOOKUP($A2,'[All.xls]29'!$A$2:$C$84,2,FALSE) for the time in
> VLOOKUP($A2,'[All.xls]29'!$A$2:$C$84,2,FALSE) for the time out
> Where 29 is the name of the sheet (representing todays date)
>
Chip Pearson has a web page working with time sheet (time in and time out)
http://www.cpearson.com/excel/overtime.htm
Could it be of help?