Help with Index, Match, XLookup with Two Variables

Mdubbers

New Member
Joined
Jun 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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
1ProjectDate WorkedHoursStaff MemberInvoice DateInvoice Number
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows
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
1ProjectDate WorkedHoursStaff MemberInvoice DateInvoice Number
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),""))
 
Solution

Mdubbers

New Member
Joined
Jun 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Yeah, that appears to solve our problem. Thank you so much, Peter!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,671
Members
425,229
Latest member
Rashid mahmood

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top