Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

✭✭✭✭✭
edited 02/12/25 in Formulas and Functions

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:

  1. In “Transactions” Sheet, Use [Pay Date]@row value to:
  2. Locate a value that is >= {RDStartDate} AND <= {RDEndDate} than [Pay Date]@row
  3. 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!!

-Neil

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions