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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: center"]Locker No
[/TD]
[TD="align: center"]Locker Status
[/TD]
[TD="align: center"]Clock no.
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Department
[/TD]
[TD="align: center"]Shift
[/TD]
[TD="align: center"]Size
[/TD]
[TD="align: center"]Overalls Last Seen
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1385
[/TD]
[TD]Mr A
[/TD]
[TD]Production
[/TD]
[TD]Days
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]1596
[/TD]
[TD]Mr B
[/TD]
[TD]Hygiene
[/TD]
[TD]Days
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]1885
[/TD]
[TD]Mr C
[/TD]
[TD]Production
[/TD]
[TD]Nights
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]1683
[/TD]
[TD]Mr D
[/TD]
[TD]Engineers
[/TD]
[TD]Days
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]1782
[/TD]
[TD]Mr E
[/TD]
[TD]Production
[/TD]
[TD]Nights
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD]1048
[/TD]
[TD]Mr F
[/TD]
[TD]Production
[/TD]
[TD]Nights
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD]1318
[/TD]
[TD]Mr G
[/TD]
[TD]Production
[/TD]
[TD]Days
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD]1724
[/TD]
[TD]Mr H
[/TD]
[TD]Despatch
[/TD]
[TD]Nights
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is a demo of Sheet 5:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Clock No.
[/TD]
[TD]Name
[/TD]
[TD]Shift
[/TD]
[TD]Start Date
[/TD]
[TD]B1 M
[/TD]
[TD]B1 F
[/TD]
[TD]B2 M
[/TD]
[TD]B2 F
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1385
[/TD]
[TD]Mr A
[/TD]
[TD][/TD]
[TD]25/05/2010
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1372
[/TD]
[TD]Mr I
[/TD]
[TD][/TD]
[TD]05/04/2011
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1575
[/TD]
[TD]Mr Z
[/TD]
[TD][/TD]
[TD]03/05/2013
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1318
[/TD]
[TD]Mr G
[/TD]
[TD][/TD]
[TD]01/04/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1782
[/TD]
[TD]Mr L
[/TD]
[TD][/TD]
[TD]17/05/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1803
[/TD]
[TD]Mr M
[/TD]
[TD][/TD]
[TD]17/05/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1815
[/TD]
[TD]Mr P
[/TD]
[TD][/TD]
[TD]02/06/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1818
[/TD]
[TD]Mr S
[/TD]
[TD][/TD]
[TD]05/06/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]1822
[/TD]
[TD]Mr X
[/TD]
[TD][/TD]
[TD]01/07/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]1683
[/TD]
[TD]Mr U
[/TD]
[TD][/TD]
[TD]01/07/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.