I need help creating a formula that I thought would be easy but is turning out to kick my be-hind, and three different Ai’s aren’t able to give me a solution either.
Formula Objective: Find the Report Name located on the Report Deadline sheet, using the [Pay Date]@row, which will be a date between two date values on Report Deadline sheet.
Sheet References in “Transactions” Sheet:
{RDStartDate} = RDStartDate Column in “Report Deadlines”
{RDEndDate} = RDEndDate Column in “Report Deadlines”
{RDReportName} = RDReportName Column in “Report Deadlines”
My Thought Process of how the formula should work:
- In “Transactions” Sheet, Use [Pay Date]@row value to:
- Locate a value that is >= {RDStartDate} AND <= {RDEndDate} than [Pay Date]@row
- Parse the first result in {RDReportName} for the row where #2 was true.
I was trying to use INDEX(MATCH but I’m not matching an exact value, instead I'm using two conditions, >= and <= so I really don’t know where to start! Help! :)
==========
Sample Data Below
Report Deadlines | | |
|---|
| | |
RDStartDate | RDEndDate | RDReportName |
01/01/25 | 01/14/25 | Report 1 |
01/15/25 | 01/31/25 | Report 2 |
02/01/25 | 02/15/25 | Report 3 |
02/16/25 | 02/28/25 | Report 4 |
03/01/25 | 03/15/25 | Report 5 |
03/16/25 | 03/31/25 | Report 6 |
Transactions | | If Formula worked, this is the result |
|---|
| | |
Pay Date | Report Name Found | EXPECTED RESULT (Manually Added) |
01/01/25 | FORMULA BELONGS HERE | Report 1 |
01/05/25 | | Report 1 |
01/12/25 | | Report 1 |
01/15/25 | | Report 2 |
01/20/25 | | Report 2 |
01/25/25 | | Report 2 |
01/28/25 | | Report 2 |
02/01/25 | | Report 3 |
02/05/25 | | Report 3 |
02/10/25 | | Report 3 |
02/15/25 | | Report 3 |
02/18/25 | | Report 4 |
02/22/25 | | Report 4 |
03/01/25 | | Report 5 |
03/05/25 | | Report 5 |
03/07/25 | | Report 5 |
03/10/25 | | Report 5 |
03/12/25 | | Report 5 |
03/15/25 | | Report 5 |
03/18/25 | | Report 6 |
03/20/25 | | Report 6 |
03/22/25 | | Report 6 |
03/25/25 | | Report 6 |
03/28/25 | | Report 6 |
03/30/25 | | Report 6 |
01/30/25 | | Report 2 |
02/28/25 | | Report 4 |
03/29/25 | | Report 6 |
03/26/25 | | Report 6 |
03/02/25 | | Report 5 |
10/02/25 | | No Result |
11/01/25 | | No Result |
12/01/25 | | No Result |
THANK YOU!!
