Some Kind of VLOOKUP?

LemonRage

New Member
Joined
Oct 6, 2008
Messages
25
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
 

Some videos you may like

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
Joined
Apr 8, 2009
Messages
17,649
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)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,330
Messages
5,528,043
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top