Some Kind of VLOOKUP?

[LemonRage]

Hello all. I am trying to clean up some data that I have and have the following two tables:


Employee ID Start Date End Date Cost Center
1 01/01/2009 05/01/2009 12345
1 05/02/2009 06/15/2010 123456
1 06/16/2010 12/31/9999 1234567
2 01/03/2011 04/01/2011 10101
2 04/02/2011 12/31/9999 101010

Employee ID Paid Date
1 03/21/2009
1 07/17/2010
2 03/13/2011
2 04/02/2011

My goal is to get the correct cost center for the second table based on the paid dates...The Paid date should fall in the range as given in the first table. I've tried a vlookup and use "1" instead of "0" for the range lookup, but I keep getting bad results. Any suggestions? -William

 

[hiker95]

LemonRage,


Somehting like this?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Employee ID	Start Date	End Date	Cost Center		Employee ID	Date	Cost Center
2	1	01/01/2009	05/01/2009	12345		1	03/21/2009	12345
3	1	05/02/2009	06/15/2010	123456		1	07/17/2010	1234567
4	1	06/16/2010	12/31/9999	1234567		2	03/13/2011	10101
5	2	01/03/2011	04/01/2011	10101		2	04/02/2011	101010
6	2	04/02/2011	12/31/9999	101010
7
Sheet1

The array formula in cell H2, confirmed with CTRL + SHIFT + ENTER (not just ENTER), and copied down:

=INDEX($D$2:$D$6,MATCH(1,IF($A$2:$A$6=F2,IF(G2>=$B$2:$B$6,IF(G2<=$C$2:$C$6,1)))))