Multiple Creteria Lookup(Between Dates)
On Jun 25, 10:18*am, imti m wrote:
Hello,
I am having problem filtering records matching the criteria.
Sheet1:(Machine Service Warrenty)
MachineID, Name, StartDate, EndDate
101 * * * *AB * *15/09/2010 14/9/2011
102 * * * *CD * *28/06/2009 *27/06/2011
103 * * * *EF * *10/12/2010 09/12/2011
Sheet2(MachineServiceInfo):
MachineID *ServiceDate
101 * * * *25/10/2010
102 * * * *26/10/2010
101 * * * *15/9/2008
101 * * * *13/12/2010
102 * * * *23/03/2011
103 * * * *28/02/2011
101 * * * *01/04/2011
101 * * * *14/02/2009
I want in sheet2 New column (ie: C) stating that yes this machine has active service warranty fall between the service period based on Sheet1. Just like below
MachineID *ServiceDate ActiveWarranty
101 * * * *25/10/2010 * Y
102 * * * *26/10/2010 * Y
101 * * * *15/9/2008 * *N
101 * * * *13/12/2010 * Y
102 * * * *23/03/2011 * Y
103 * * * *28/02/2011 * Y
101 * * * *01/04/2011 * Y
101 * * * *14/02/2009 * N
How to get this result using vlookup or some script in excel.
Thanks
You could do this within a looping macro using FINDNEXT ord
use SUMPRODUCT formula
|