+ Reply to Thread
Results 1 to 6 of 6

Finding value of date within date range + one other criteria

  1. #1
    Registered User
    Join Date
    11-10-2017
    Location
    Boston, MA
    MS-Off Ver
    2016
    Posts
    15

    Finding value of date within date range + one other criteria

    Sample sheet at https://docs.google.com/spreadsheets...it?usp=sharing

    Let's say I have a cleaning company. OK I have one data set which has 2 columns, lists people and the date they were serviced:
    CLIENT DATE-OF-SERVICE
    Smith 1/3/2017
    Johnson 3/4/2017
    Jones 10/4/2016
    etc.

    Then I have another data set, 4 columns, that shows invoice #s with the date range each invoice covers
    INVOICE# CLIENT DATESTART DATEEND
    1001 Johnson 3/1/2017 3/31/2017
    1002 Adams 4/1/2016 4/30/2016
    1003 Williams 10/1/2017 10/30/2017
    etc.

    How could I create a formula that would match the last name and find the corresponding invoice# for a given date of service? I saw some formulae that can lookup a value based on a date range but the tricky part is integrating the last name to make sure we get the right client's invoice. Multiple invoices may cover the same date range - we want the one that corresponds to the given last name.

    I had actually posted this to the Excel section just because I am a ignorant newbie - it was suggested using C2 =IFERROR(INDEX(G$2:G$4,MATCH(1,INDEX((A2=H$2:H$4)*(B2>=I$2:I$4)*(B2<=J$2:J$4),0),0)),"No Match") With first data set in columns A:B and your second data set in columns G:J, to try this in C2. But I couldnt get it to work on GS. I did not try in Excel.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2016
    Posts
    12,514

    Re: Finding value of date within date range + one other criteria

    Many people on this forum are reluctant to download files from other sites fearing potential malware. We urge you to upload your files through Excel Forum. Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    11-10-2017
    Location
    Boston, MA
    MS-Off Ver
    2016
    Posts
    15

    Re: Finding value of date within date range + one other criteria

    Sorry, yes I posted the data above so that no Excel file or Google Sheet would be needed hopefully, but have attached an exported xlsx from my Google sheet too.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-10-2017
    Location
    Boston, MA
    MS-Off Ver
    2016
    Posts
    15

    Re: Finding value of date within date range + one other criteria

    Well I dont know who but someone logged into my Google Sheet and seems to have solved it with "=IFERROR(1/(1/SUMIFS(G:G,H:H,A2,I:I,"<="&B2,J:J,">="&B2)),"No Match")" - seems like it works! Thank you mystery Excel Master!

  5. #5
    Registered User
    Join Date
    11-10-2017
    Location
    Boston, MA
    MS-Off Ver
    2016
    Posts
    15

    Re: Finding value of date within date range + one other criteria

    OK well one weird thing. I have some invoices with potentially the same or overlapping dates, same name but different invoice numbers. Looks like the formula will add the invoice #s together. Is there a way to flag this so I will notice when this happens. This is rare so not worth doing more than flagging it somehow so I notice it. Usually the addition of the 2 invoice #s will result in an invoice # that is out of range but still just in case would be good to know if there is more than one match either by conditional formatting or some other message?

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,173

    Re: Finding value of date within date range + one other criteria

    Not sure that I can solve, however it would be easier for someone to make an attempt if you would upload a sample of the spreadsheet showing the behavior that you describe in post #5.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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