Finding value of date within date range + one other criteria

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.

Hello and welcome to the forum.

With your first data set in columns A:B and your second data set in columns G:J, try this in C2:

=IFERROR(1/(1/SUMIFS(G:G,H:H,A2,I:I,"<="&B2,J:J,">="&B2)),"No Match")

Note that this will only work if the INVOICE #'s are actual numbers and there are no duplicates like in your example.

Here's another, more robust formula:

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")

OK is it sacrilege to be trying these in Google Sheets? Am I missing something? I can't seem to get it to work in: https://docs.google.com/spreadsheets...it?usp=sharing

You didn't mention that you are using Google Sheets. While Excel and Google Sheets may look similar, they are quite different and it is important to note.

Also, you posted this in the "Excel Formulas & Functions" area.

A Google Sheets question should have been posted here: https://www.excelforum.com/for-other...mobile-os-etc/

Unfortunately, I cannot help with Google Sheets.

My apologies. I thought the formulae were largely the same and worked the same. Will post this over in the other section. May need to move to regular Excel if it doesnt work in GS.

Keep it here and ask a moderator to move it for you. Do not make a duplicate post.

If you have Excel, write these formulas in Excel then open it in Google Sheets. The formulas will be converted (if required) for you.