# Help with Index, Match, XLookup with Two Variables

#### Mdubbers

##### New Member
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
ABCDEF
2ABC01/15/201Fred
3ABC01/31/203Daphne
4ABC02/15/204Shaggy
5ABC03/02/201Velma
6ABC03/31/202Velma
7ABC04/08/205Velma
8ABC04/27/203Fred
9ABC05/03/201Fred
10ABC06/15/207Shaggy
11XYZ02/01/212Shaggy
12XYZ02/15/216Velma
13XYZ02/16/211Velma
14XYZ02/27/217Velma
15XYZ02/28/214Shaggy
16XYZ03/05/212Velma
17XYZ03/10/211Shaggy
18XYZ03/15/214Daphne
Time Entries

Invoices
Example Excel Formula Needed Days to Billing_V3.xlsx
ABCD
1ProjectBilling End DateInvoice DateInvoice Number
2ABC01/31/2001/31/201015
3ABC03/31/2004/07/201025
4ABC04/30/2005/15/201032
5ABC05/31/2005/31/201035
6ABC08/31/2009/10/201083
7XYZ03/31/2104/15/211125
8XYZ04/30/2104/30/211134
9XYZ05/31/2105/31/211167
Invoices
Cell Formulas
RangeFormula
B2:B9B2=IF(C2=EOMONTH(C2,0),C2,EOMONTH(C2,-1))

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
ABCDEF
2ABC15-Jan-201Fred31-Jan-201015
3ABC31-Jan-203Daphne31-Jan-201015
4ABC15-Feb-204Shaggy07-Apr-201025
5ABC02-Mar-201Velma07-Apr-201025
6ABC31-Mar-202Velma07-Apr-201025
7ABC08-Apr-205Velma15-May-201032
8ABC27-Apr-203Fred15-May-201032
9ABC03-May-201Fred31-May-201035
10ABC15-Jun-207Shaggy10-Sep-201083
11XYZ01-Feb-212Shaggy15-Apr-211125
12XYZ15-Feb-216Velma15-Apr-211125
13XYZ16-Feb-211Velma15-Apr-211125
14XYZ27-Feb-217Velma15-Apr-211125
15XYZ28-Feb-214Shaggy15-Apr-211125
16XYZ05-Mar-212Velma15-Apr-211125
17XYZ10-Mar-211Shaggy15-Apr-211125
18XYZ15-Mar-214Daphne15-Apr-211125
19XYZ04-Jun-21
Time Entries
Cell Formulas
RangeFormula
E2:E19E2=IFNA(MIN(FILTER(Invoices!C\$2:C\$9,(Invoices!A\$2:A\$9=A2)*(Invoices!B\$2:B\$9>=B2),NA())),"")
F2:F19F2=IF(E2="","",FILTER(Invoices!D\$2:D\$9,(Invoices!A\$2:A\$9=A2)*(Invoices!C\$2:C\$9=E2),""))

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

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

Replies
0
Views
227
Replies
3
Views
244
Replies
2
Views
446
Replies
0
Views
720
Replies
4
Views
281

1,196,502
Messages
6,015,585
Members
441,902
Latest member
alhaste

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back