+ Reply to Thread
Results 1 to 5 of 5

Formula to look for a match based on 2 criteria

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Formula to look for a match based on 2 criteria

    hello,

    I'm trying to create a formula that returns a value based on a match with 2 criteria. 1 of these criteria is based on wether a date falls within a date range but only if the other criteria is also met.

    See example tables below. The formula should go in table 2. The formula should look for a row in table 1 where the 2 criteria are met:
    -the ID number matches, and
    -the date in one of the rows for the matching ID number falls within the given date range.
    If this is the case the value in the last column of the row where the match occurs should be returned.

    I've tried a number of different combinations of INDEX, MATCH and SUMPRODUCT, the difficulty seems to be that in table 1 the same ID can have multiple date ranges. Furthermore different ID's can have overlapping date ranges. The same ID will never have a duplicate date range though.

    The formula that seems to come pretty close is:
    {=INDEX($D$2:$D$8,MATCH(1,($A$2:$A$8=$A12)*(SUMPRODUCT(($B$2:$B$8<=C$11)*($C$2:$C$8>=C$11))=1),0))}

    However it seems to return a value disregarding the ID number.

    TABLE 1
    ID Start date End date Code
    A 22/08/2012 29/08/2012 ABC
    B 22/07/2012 23/07/2012 DEF
    B 18/09/2012 18/09/2012 ABC
    B 25/09/2012 26/09/2012 ABC
    C 13/06/2012 03/07/2012 XYZ
    C 22/08/2012 24/08/2012 XYZ
    C 24/09/2012 01/10/2012 ABC

    TABLE 2
    Date 21/07/2012 22/07/2012 23/07/2012 24/07/2012
    A N/A N/A N/A N/A
    B N/A DEF DEF N/A
    C N/A N/A N/A N/A

    TABLE above is showing the values that the formuka should return. The example formula will return the codes (ABC, DEF etc) in every row for dates 22/07 and 23/07 regardless of the ID number.

    BTW, I'm using Excel 2010 on Windows 7.

    Any suggestions on how to amedn the formula would be much appreciated!
    Thanks.
    Last edited by wsr2006; 01-14-2013 at 09:01 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Formula to look for a match based on 2 criteria

    this woudl be eaiser to work on with a workbook
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula to look for a match based on 2 criteria

    Try this Array Formula (enter with Cntrl+Shft+Enter, not just Enter) in B12:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag Down And Across

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Registered User
    Join Date
    11-30-2011
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Formula to look for a match based on 2 criteria

    hi dredwolf,

    too easy! that works just great, thanks!!

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula to look for a match based on 2 criteria

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1