Find Last Non-zero ( Non-blank) Row Meeting Criteria in Google Sheets-CodePudding

I am trying to get the last non-zero, non-blank value of a row within a column (Column F in my example image) wherein that row ALSO matches a Campaign name (Column D).

Most search results yield an Excel-specific variation of =LOOKUP(1,1/(L:L>0),L:L), but this doesn't work in Google Sheets.

I am trying to solve for Cell F23 = 2374.

I found and modified a formula which returns the last non-zero, non-blank value within a column reliably, but I don't know where to mix the additional filter (basically, D$2:D22 = D23) into the INDEX function.

Here is what I'm working with:

=if(

{{separate_formula_that_fetches_value_from_other_sheet}})=0, 

INDEX((FILTER(D$2:F22,NOT(ISBLANK(D$2:F22)))), (ROWS(FILTER(D$2:F22,NOT(ISBLANK(D$2:F22))))),3)

)

Here is the example table:

Thank you for any help!

CodePudding user response：

If you are trying to find inside RANGE B2:F22 which...

value in Column F is not empty and greater than 0, and
value in Column D matches D23,

try this, didn't test it, but it should work I think:

=LAMBDA(FILTER,
 INDEX(FILTER,COUNTA(FILTER))
)(FILTER($F$2:$F$22,$F$2:$F$22>0,$D$2:$D$22=$D23))