Take previous value if false

Hi there,

I have a long if function, where if "false," I want to grab the closest previous cell (in the same column) that has a value.

Here is my formula: =IF(SUM($B4:$L4)>=45,SUM(SUMIF($B4:$L4,LARGE($B4:$L4,{1,2,3}),$Z4:$AJ4))INSERT IF FALSE HERE!)+IF(SUM($B4:$L4)>=45,SUM(SUMIF($B4:$L4,SMALL($B4:$L4,{1,2,3}),$Z4:$AJ4)))

To walk you through what I've done: If the sum of array 1 >=45, then sum the three cells in array 2 based on their position in array 1. The first If function sums the three largest criteria in array 1, the second if function sums the smallest.

The important part is where I've written INSERT IF FALSE HERE. In the standard IF( condition, [value_if_true], [value_if_false] ) formula, I want the [value_if_false] to express that, if the sum of array 1 is less than 45(condition), pull the closest previous cell with a value in it.

For example, in the sheet attached, since cell AL6 does not have a value (i.e. because, SUM($B6:$L6) does not =45), I want the 0 to be replaced with the closest previous cell with a value, which in this case is AL3.


I would think I need to use some type of vlookup...