Finding last non-zero value given a criteria

[rpradeepan]

Hi guys,

I have a dataset which has a column of customer numbers and then columns of revenue by date (B to CX) and then also have a column (CZ) of date for an email sent. I want to look for the last date of revenue before the date of action. So if the email was sent on 06/11/2017 I want to obtain the last revenue geenrated by the customer before that date. Is there a formula using =LOOKUP(2,1/(A2:CX2<>0),$A$1:$CX$1) but with a condition?

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe...
=LOOKUP(2,1/((A2:CX2<>0)*(CZ2>$A$1:$CX$1)),$A$1:$CX$1)

M.