Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 25th 11, 04:18 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 4
Default Multiple Creteria Lookup(Between Dates)

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





  #2   Report Post  
Old June 25th 11, 04:59 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default Multiple Creteria Lookup(Between Dates)

On Jun 25, 10:18*am, imti m wrote:
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



  #3   Report Post  
Old June 25th 11, 05:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Multiple Creteria Lookup(Between Dates)

On Jun 25, 8:18*am, imti m wrote:
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.
  #4   Report Post  
Old June 25th 11, 05:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default 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.

  #5   Report Post  
Old June 25th 11, 06:13 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 4
Default [....][....][....]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.




  #6   Report Post  
Old June 25th 11, 06:34 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 4
Default [....][....][....]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


  #7   Report Post  
Old June 25th 11, 06:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default [....][....]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.

  #8   Report Post  
Old June 25th 11, 10:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default [....]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
  #9   Report Post  
Old June 26th 11, 02:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 4
Default [....][....][....]In C2:=3DIF(AND(VLOOKUP(A2,Sheet1!

Any update




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple lookup between two dates Ales Excel Worksheet Functions 2 February 10th 10 04:32 PM
Lookup a location # with multiple dates Andrea Excel Worksheet Functions 2 November 9th 09 09:56 PM
How to use the IF function to resolved more than one creteria tech1NJ Excel Worksheet Functions 4 July 31st 08 09:06 PM
SUMIF with 2 creteria one Name one DAte Rolf Excel Discussion (Misc queries) 3 December 7th 06 12:51 AM
lookup based on multiple dates [email protected] Excel Worksheet Functions 1 May 30th 06 09:35 PM


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017