Looking up duplicate values

[Pauljj]

Hopefully not too difficult ?!?!

The question here is E7, which is a payroll number, this can appear multiple times, so in the formula below, which will go from G5 across to M5, if it picks up the same payroll number it needs to pick up the second value, instead of the first

To explain Further this is the source data

LeaveTemplate.xls
	B	C	D	E	F	G	H
1	3803		XLE	DHM		03/01/2010	03/01/2010
2	4006		XDE	FL		04/11/2009	13/11/2009
3	3803		XLE	LMM		07/01/2010	08/01/2010
4	5034		XLE	PHM		06/11/2009	15/11/2009
LeaveTemplate

Because the value 3803 (column B) appears twice, my VLOOKUP won't work. How can I pick up both?


IF(AND(R$4>=VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,6,0),R$4<=VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,7,0)),VLOOKUP($E7,LeaveTemplate'!$B$1:$H$50,3,0),BL7))

 

[c_m_s_jr]

Is this what you are looking for?

Excel Workbook
	A	B	C	D	E	F	G
1	3803		XLE	DHM		3/1/2010	3/1/2010
2	4006		XDE	FL		4/11/2009	13/11/2009
3	3803		XLE	LMM		7/1/2010	8/1/2010
4	5034		XLE	PHM		6/11/2009	15/11/2009
5
6
7	3803		8/1/2010
Sheet1

 

[Pauljj]

I think this might be heading in the right direction, however, its probably a good idea to show you where the data will appear

Bournemouth Roster 2010.xls
	E	F	G	H	I	J	K	L	M
3			Sun	Mon	Tue	Wed	Thu	Fri	Sat
4	StaffNo	PatterNo	03Jan	04Jan	05Jan	06Jan	07Jan	08Jan	09Jan
5	4751	1	IS	IS	IS	IS	1	1	X
6
7	4566	31	X	1	1	1	X	X	1
8	5763	11	X	1	1	1	1	1	X
January

As you can see when on row 5 I get the lookup for staff no 4751. It will pull the first lookup which is between the 03-06 Jan but if from the source data, 4751 appears again for dates between 08-09 Jan, then it doesn't show

 

[Pauljj]

Any ideas please ?

 

[c_m_s_jr]

I guess I need to understand what it is you are doing with the information from the main spreadsheet with the data in post #3.