Finding Values - Can't get LOOKUP to work

[GazNicki]

Hi all,

I have an Excel Workbook with 6 worksheets in it. The workbook is to monitor Staff Lockers. Sheets 1 - 4 are lists of lockers in different areas. Sheet 5 is a list of all staff with their clock numbers. Sheet 6 for now is blank.

Here is a demo of sheet 1:

	A	B	C	D	E	F	G	H
2	Locker No	Locker Status	Clock no.	Name	Department	Shift	Size	Overalls Last Seen
3	1		1385	Mr A	Production	Days
4	2		1596	Mr B	Hygiene	Days
5	3		1885	Mr C	Production	Nights
6	4		1683	Mr D	Engineers	Days
7	5		1782	Mr E	Production	Nights
8	6		1048	Mr F	Production	Nights
9	7		1318	Mr G	Production	Days
10	8		1724	Mr H	Despatch	Nights

<tbody>
</tbody>

Here is a demo of Sheet 5:

	A	B	C	D	E	F	G	H
1	Clock No.	Name	Shift	Start Date	B1 M	B1 F	B2 M	B2 F
2	1385	Mr A		25/05/2010
3	1372	Mr I		05/04/2011
4	1575	Mr Z		03/05/2013
5	1318	Mr G		01/04/2014
6	1782	Mr L		17/05/2014
7	1803	Mr M		17/05/2014
8	1815	Mr P		02/06/2014
9	1818	Mr S		05/06/2014
10	1822	Mr X		01/07/2014
11	1683	Mr U		01/07/2014

<tbody>
</tbody>

As you can see from the examples, sheets 1 - 4 are listed sorted by Locker Number. Sheet 5 is listed by Start Date of Staff.
I would like to be able to do the following:

In sheet 5 cell E2 I would like to be able to search Sheet 1 and look for the Clock No. If the Clock No is there, then I would like the Locker Number to appear in the cell E2. If not, then a simple #N/A is ok.
Cell F2 would do the same thing, but search sheet 2, G2 would search sheet 3 and H2 would search sheet 4.

I can't seem to get any of the lookups working, and I don't know why. Could anyone please help me with this??

Thanks,

Gaz

 

[skywriter]

Try this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Clock No.	Name	Shift	Start Date	B1 M	B1 F	B2 M	B2 F
2	1385	Mr A		25/05/2010	1
3	1372	Mr I		5/4/2011	#N/A
4	1575	Mr Z		3/5/2013	#N/A
5	1318	Mr G		1/4/2014	7
6	1782	Mr L		17/05/2014	5
7	1803	Mr M		17/05/2014	#N/A
8	1815	Mr P		2/6/2014	#N/A
9	1818	Mr S		5/6/2014	#N/A
10	1822	Mr X		1/7/2014	#N/A
11	1683	Mr U		1/7/2014	4
Sheet5

 

[GazNicki]

Thanks Skywriter, that seems to do the trick

 

[skywriter]

My pleasure.