Multiple Creteria Lookup(Between Dates)

[imti m]

Hello,
I am having problem filtering records matching the criteria.
Sheet1Machine 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

 

[Don Guillett]

Hello,
I am having problem filtering records matching the criteria.
Sheet1Machine 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

 

[joeu2004]

Hello,
I am having problem filtering records matching the criteria.
Sheet1Machine 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.

 

[joeu2004]

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

Typo. That should be:

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

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.

 

[imti m]

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.

 

[imti m]

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

 

[joeu2004]

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.

 

[Don Guillett]

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

 

[imti m]

Any update