Help with Index, Match, XLookup with Two Variables

[Mdubbers]

Hello! Thanks in advance to anyone reading or helping with this! We have a challenge that we keep spinning our wheels on and could use help.

There are two sheets of data:
TimeEntries: Time Entry info logged by employees on various Projects. This has one Time Entry per row and notably identifies the Project and the Date Worked.
Invoices: Invoices that have been sent out for various Projects. This has one Invoice per row and notably identifies the Project, the Billing End Date, the Invoice Date, and the Invoice Number.

We need a formula or formulas to link each Time Entry record with an Invoice. Specifically, we want the Invoice Date and Invoice Number on which each Time Entry was billed, which we'll derive using logic explained below.

For example, in TimeEntries Cell E2, the formula would first look up the value in Cell A2 (Project "ABC") within the Invoices sheet, and determine the range that applies based on Invoices Column A (finding "ABC" in A2:A6).

Then, once it knows we're limited to rows 2-6, it would compare the Date Worked in TimeEntries Column B to the Billing End Date in Invoices Column B for that project (i.e only in rows 2:6). It would find the record within that Project's list of invoices that has a Billing End Date equal to or the closest later date within that Project. (If there are no Invoices dated after a Time Entry, it can return a null value or zero, meaning it hasn't yet been invoiced.)

Example 1: In TimeEntries Row 2, there is a Date Worked of Jan 15, 2020. Looking at the values in Invoices rows 2-6, it should see that the Billing End Date of Jan 31, 2020 is the next closest value to the Date Worked and thus Invoice Number = 1015 and Invoice Date = 1/31/2020.

Example 2: TimeEntries Row 3 has a Date Worked of Jan 31, 2020. This exactly matches one the Billing End Date values and thus should also yield Invoice Number 1015 with an Invoice Date of Jan 31, 2020.

Example 3: TimeEntries Row 4 is where it gets tricky. Invoices are not always sent out every month. So, a Time Entry with a Date Worked of Feb 7, 2020 on Project ABC would need to find the next closest future Billing End Date. Since 1/31/2020 is before the Time Entry's Date Worked, we need to go to the next row and yield a result of Invoice Number 1025 with an Invoice Date of April 7, 2020.

Can anyone help figure out how to achieve this? It feels like this should be achievable, but we just can't quite figure it out. Thanks!

TimeEntries

Example Excel Formula Needed Days to Billing_V3.xlsx
	A	B	C	D	E	F
1	Project	Date Worked	Hours	Staff Member	Invoice Date	Invoice Number
2	ABC	01/15/20	1	Fred
3	ABC	01/31/20	3	Daphne
4	ABC	02/15/20	4	Shaggy
5	ABC	03/02/20	1	Velma
6	ABC	03/31/20	2	Velma
7	ABC	04/08/20	5	Velma
8	ABC	04/27/20	3	Fred
9	ABC	05/03/20	1	Fred
10	ABC	06/15/20	7	Shaggy
11	XYZ	02/01/21	2	Shaggy
12	XYZ	02/15/21	6	Velma
13	XYZ	02/16/21	1	Velma
14	XYZ	02/27/21	7	Velma
15	XYZ	02/28/21	4	Shaggy
16	XYZ	03/05/21	2	Velma
17	XYZ	03/10/21	1	Shaggy
18	XYZ	03/15/21	4	Daphne
Time Entries

Invoices

Example Excel Formula Needed Days to Billing_V3.xlsx
	A	B	C	D
1	Project	Billing End Date	Invoice Date	Invoice Number
2	ABC	01/31/20	01/31/20	1015
3	ABC	03/31/20	04/07/20	1025
4	ABC	04/30/20	05/15/20	1032
5	ABC	05/31/20	05/31/20	1035
6	ABC	08/31/20	09/10/20	1083
7	XYZ	03/31/21	04/15/21	1125
8	XYZ	04/30/21	04/30/21	1134
9	XYZ	05/31/21	05/31/21	1167
Invoices

Cell Formulas
Range	Formula
B2:B9	B2	=IF(C2=EOMONTH(C2,0),C2,EOMONTH(C2,-1))

 

[Peter_SSs]

Welcome to the MrExcel board!

See if these do what you want. I have added one more row to Time Entries to cover ..

If there are no Invoices dated after a Time Entry, it can return a null value

Mdubbers.xlsm
	A	B	C	D	E	F
1	Project	Date Worked	Hours	Staff Member	Invoice Date	Invoice Number
2	ABC	15-Jan-20	1	Fred	31-Jan-20	1015
3	ABC	31-Jan-20	3	Daphne	31-Jan-20	1015
4	ABC	15-Feb-20	4	Shaggy	07-Apr-20	1025
5	ABC	02-Mar-20	1	Velma	07-Apr-20	1025
6	ABC	31-Mar-20	2	Velma	07-Apr-20	1025
7	ABC	08-Apr-20	5	Velma	15-May-20	1032
8	ABC	27-Apr-20	3	Fred	15-May-20	1032
9	ABC	03-May-20	1	Fred	31-May-20	1035
10	ABC	15-Jun-20	7	Shaggy	10-Sep-20	1083
11	XYZ	01-Feb-21	2	Shaggy	15-Apr-21	1125
12	XYZ	15-Feb-21	6	Velma	15-Apr-21	1125
13	XYZ	16-Feb-21	1	Velma	15-Apr-21	1125
14	XYZ	27-Feb-21	7	Velma	15-Apr-21	1125
15	XYZ	28-Feb-21	4	Shaggy	15-Apr-21	1125
16	XYZ	05-Mar-21	2	Velma	15-Apr-21	1125
17	XYZ	10-Mar-21	1	Shaggy	15-Apr-21	1125
18	XYZ	15-Mar-21	4	Daphne	15-Apr-21	1125
19	XYZ	04-Jun-21
Time Entries

Cell Formulas
Range	Formula
E2:E19	E2	=IFNA(MIN(FILTER(Invoices!C$2:C$9,(Invoices!A$2:A$9=A2)*(Invoices!B$2:B$9>=B2),NA())),"")
F2:F19	F2	=IF(E2="","",FILTER(Invoices!D$2:D$9,(Invoices!A$2:A$9=A2)*(Invoices!C$2:C$9=E2),""))

 

[Mdubbers]

Yeah, that appears to solve our problem. Thank you so much, Peter!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.