LOOKUP Formula based on result between date range

Richard Heath

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

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

Richard Heath

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. 

 

Paul Newcome

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

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))}

Paul Newcome

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

Amazing. Thanks for that. 

I really appreciate your assistance with this one. 

Paul Newcome

Happy to help!