Multiple Creteria Lookup(Between Dates)
 

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

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

Multiple Creteria Lookup(Between Dates)
 

On Jun 25, 8: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
[....]
Sheet2(MachineServiceInfo):
MachineID *ServiceDate
101 * * * *25/10/2010
102 * * * *26/10/2010
101 * * * *15/9/2008
[....]
I want in sheet2 New column (ie: C)
[....]
MachineID *ServiceDate ActiveWarranty
101 * * * *25/10/2010 * Y
102 * * * *26/10/2010 * Y
101 * * * *15/9/2008 * *N

In C2:

=IF(AND(VLOOKUP(A2,Sheet1!$A:$D,3)<=B2,
B2<=VLOOKUP(A2,Sheet1!$A:$D,4)),"Y","N")

Alternatively, put =MATCH(A2:Sheet1!$A:$A) into a helper cell (D2),
and in C2:

=IF(AND(INDEX(Sheet1!$C:$C,D2)<=B2,
B2<=INDEX(Sheet1!$D:$D,D2)),"Y","N")

Those formulas assume that every machineID in column Sheet2!A:A
appears in Sheet1!A:A; that is, no errors.

Multiple Creteria Lookup(Between Dates)
 

On Jun 25, 9:04*am, joeu2004 wrote:
Alternatively, put =MATCH(A2:Sheet1!$A:$A) into a helper cell

Typo. That should be:

=MATCH(A2,Sheet1!$A:$A)

I wrote:
Those formulas assume that every machineID in column
Sheet2!A:A appears in Sheet1!A:A; that is, no errors.

They also assume that Sheet1 data is sorted with column A in ascending
order.

If that is not the case, you will need 0 in the 4th VLOOKUP parameter
and the 3rd MATCH parameter. However, it would be more efficient to
ensure that Sheet1 data is sorted.

[....][....][....]In C2:=3DIF(AND(VLOOKUP(A2,Sheet1!
 

Thanks for the reply.
I am getting error. Not sure what I am making mistake. I have 4396 rows. Please check the code.

=3DIF(AND(INDEX(Sheet1!$C$2:$C$4396,D2)<=3DB2,B2<= 3DINDEX(Sheet1!$D$2:$D$4396,D2)),"Y","N")

Any answer will be appreciated.

[....][....][....]In C2:=3DIF(AND(VLOOKUP(A2,Sheet1!
 

I am getting this error:
The formula you type contains error and highlighting 3DIF. Not sure what is the issue

[....][....]In C2:=3DIF(AND(VLOOKUP(A2,Sheet1!
 

On Jun 25, 10:13*am, imti m wrote:
Thanks for the reply.
I am getting error. Not sure what I am making mistake.
[....]
=3DIF(AND(INDEX(Sheet1!$C$2:$C$4396,D2)
<=3DB2,B2<=3DINDEX(Sheet1!$D$2:$D$4396,D2)),"Y","N ")

When you copy-and-paste, for some reason equal signs ("=") are turning
into the MIME representation ("=3D").

You should turn off "rich text" (or turn on "plain text") in the
application you use to view these newsgroups.

Alternatively, copy-and-paste into Notepad, then copy from Notepad and
paste into Excel.

FYI, that paragidm (pasting into Notepad first) is often required when
copying form online sources. So it is a useful procedure to get in
the habit of doing.

[....]In C2:=3DIF(AND(VLOOKUP(A2,Sheet1!
 

On Jun 25, 12:43*pm, joeu2004 wrote:
On Jun 25, 10:13*am, imti m wrote:

Thanks for the reply.
I am getting error. Not sure what I am making mistake.
[....]
=3DIF(AND(INDEX(Sheet1!$C$2:$C$4396,D2)
<=3DB2,B2<=3DINDEX(Sheet1!$D$2:$D$4396,D2)),"Y","N ")

When you copy-and-paste, for some reason equal signs ("=") are turning
into the MIME representation ("=3D").

You should turn off "rich text" (or turn on "plain text") in the
application you use to view these newsgroups.

Alternatively, copy-and-paste into Notepad, then copy from Notepad and
paste into Excel.

FYI, that paragidm (pasting into Notepad first) is often required when
copying form online sources. *So it is a useful procedure to get in
the habit of doing.

If desired, send me your file to dguillett1 @gmail.com

[....][....][....]In C2:=3DIF(AND(VLOOKUP(A2,Sheet1!
 

Any update