[Solved] Find Last Non-Empty Cell value

[mfaisal.ce]

Dear,

I have a file with below

Sheet Nameailywise
Value in Column: AV
Date in clumn: A

there is another sheet name "Summary"
in that sheet, for each month how to find the last non-empty cell value in Colum in AV from DailyWise Sheet where Date in Column A is matches with Date in Summary Sheet.

I am able to find for a particular date but not range of date.

Kindly guide,

Regards,

 

[shamimarita]

Hello Faisal,

I used an array formula to find the last non-empty cell value in column AV from the "Dailywise" sheet for each month, where the date in column A matches the corresponding date in the "Summary" sheet. Used LOOKUP function in the formula:
=LOOKUP(2, 1/((DailyWise!$A$5:$A$1000>=B5)*(DailyWise!$A$5:$A$1000<EDATE(B5,1))*(DailyWise!$AV$5:$AV$1000<>"")), DailyWise!$AV$5:$AV$1000)

The LOOKUP function searches for the last occurrence of the condition being true and matching date, non-empty value then retrieves the corresponding value in column AV.



Download the Excel File:

 

[mfaisal.ce]

Hello Faisal,

I used an array formula to find the last non-empty cell value in column AV from the "Dailywise" sheet for each month, where the date in column A matches the corresponding date in the "Summary" sheet. Used LOOKUP function in the formula:
=LOOKUP(2, 1/((DailyWise!$A$5:$A$1000>=B5)*(DailyWise!$A$5:$A$1000<EDATE(B5,1))*(DailyWise!$AV$5:$AV$1000<>"")), DailyWise!$AV$5:$AV$1000)

The LOOKUP function searches for the last occurrence of the condition being true and matching date, non-empty value then retrieves the corresponding value in column AV.

View attachment 1594

Download the Excel File:

Thanks a lot. It worked.

regards,

 

[shamimarita]

You are most welcome.