HELP! Formula to Find Matching Value Between two Dates in another sheet

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 |
-Neil
Best Answer
-
INDEX/COLLECT should work.
=INDEX(COLLECT({Column To Pull Over}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)
Answers
-
INDEX/COLLECT should work.
=INDEX(COLLECT({Column To Pull Over}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)
-
@Paul Newcome Thanks for the idea but If that solution works, I must have it wrong.
Your suggestion…
=INDEX(COLLECT({Column To Pull Over}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)
…becomes…
=INDEX(COLLECT({RDReportName}, {RDStartDate}, <=[Pay Date]@row, {RDEndDate}, >=[Pay Date]@row), 1)
…Result: WORKING
THANK YOU!
-Neil
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.4K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 492 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!