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.
Bookmarks