# Some Kind of VLOOKUP?

#### LemonRage

##### New Member
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

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### hiker95

##### Well-known Member
LemonRage,

Somehting like this?

Excel Workbook
ABCDEFGH
1Employee IDStart DateEnd DateCost CenterEmployee IDDateCost Center
2101/01/200905/01/200912345103/21/200912345
3105/02/200906/15/2010123456107/17/20101234567
4106/16/201012/31/99991234567203/13/201110101
5201/03/201104/01/201110101204/02/2011101010
6204/02/201112/31/9999101010
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)))))

Replies
6
Views
47
Replies
4
Views
56
Replies
2
Views
65
Replies
3
Views
73
Replies
5
Views
52