# Finding value of date within date range + one other criteria

1. ## 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. ## 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. ## 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.

4. ## 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. ## 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. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

#### 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