+ 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
    13

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    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
    13

    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
    13

    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 Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    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)

Similar Threads

  1. Finding value of date within date range + one other criteria
    By firepitz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-11-2017, 10:50 AM
  2. [SOLVED] finding oldest real date in a date range
    By JakeMann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-01-2017, 04:50 AM
  3. Replies: 4
    Last Post: 04-28-2017, 11:06 AM
  4. HELP with Finding similar values in same date range -- Multiple Criteria
    By yessuz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2016, 04:33 AM
  5. Finding Nearest date in a range based on critera date
    By skate1991 in forum Excel General
    Replies: 5
    Last Post: 07-22-2015, 02:10 PM
  6. IF function finding a date in a date range
    By KelleyScott in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2015, 06:26 AM
  7. Finding a date in a text string and comparing it to a date range
    By ABARAN1000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2012, 02:55 PM

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