LOOKUP Formula based on result between date range

Richard Heath · 05/15/19

Hello,

I've tried to create a LOOKUP formula that will provide the "Approved Value" if the "Date Certified" calls between the period stipulated in "Period Start" and "Period End," without much success.

Based on the screen-grab, only two values should be returned if the formula is successful. (I've manually entered these to show the values.).

Assistance would be appreciated.

Thanks

Richard.

Paul Newcome · 05/16/19

Are you trying to display both values or add them together or just count that there are two values?

Richard Heath · 05/16/19

Hi Paul,

I'm looking to only display one value in the Parent Row being the Approved Claim value.

I've updated the Screen Shot to attempt to clarify things. I am hoping to include a formula that looks at the Date Certified column, determine if the Date Certified value falls between the Period Start and Period End values and if it does, return the adjacent cell in the Approved Claim column to the Parent cell as highlighted in Yellow.

As an example, the $2,000 approved on the 15th of May 2019 occurred during the defined period, therefore the value ($2,000) should appear under “Approved This Period.” (Yellow Box). I've suggested where else the FORMULA should provide a result (Yellow Boxes)

Thanks for your help.

Regards,

Richard.

Updated Capture.JPG

Paul Newcome · 05/17/19

Ok. I am understanding much better now. You want the blue rows to display the value (if any) of the white rows underneath of it that falls within the specified period.

This leads me to two more questions...

1. Are the white rows actually indented below the blue rows as an actual parent/child relationship?

2. What if there is more than one value in any given section that meets the given date criteria? For example... In your first section ($11,000.00), what if the $1,000.00 row also happened to fall within your date range? Would you want to display $2,000.00 and $1,000.00 or would you want $3,000.00 displayed?

Richard Heath · 05/19/19

Hi Paul,

In response to you queries;

1. Yes they are indented - I've added a new capture showing the entire screen.

2. The chance that two values were met the criteria for the month is low, but if it did happen, they should probably add together.

I have managed to create the formula in Excel, but being an Array Formula, it doesn't appear to carry over to Smartsheet. I've successfully changed dates in the Period Start/End fields which then correctly alters the "Approved this Period" field.

={INDEX(F$3:F$5,MATCH(1,IF(G3:G5>=J$2,IF(G3:G5<=K$2,1)),0))}

Excel Version.JPG

Paul Newcome · 05/20/19

Ok. To account for the off chance that two lines would overlap in dates, we will use a basic SUMIFS instead of an INDEX/MATCH.

Try something along the lines of...

=SUMIFS(CHILDREN([Approved Claim]@row), CHILDREN([Date Certified]@row), AND(@cell >= [Period Start]$1, @cell <= [Period End]$1))

Richard Heath · 05/20/19

Amazing. Thanks for that.

I really appreciate your assistance with this one.

Paul Newcome · 05/21/19

Happy to help!

Formulas and Functions

Formulas and Functions

LOOKUP Formula based on result between date range

Comments

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

CountIF contains name from a contact list help needed

Formula for averaging days

Weekly rotating roles formulas