How to find last value in a month

[MyStix01]

Hi,

Looking for a little help to twitch my formula so I'd get the right value.

If you can kindly look at the attached, I've been trying to work out the correct formula for sheet Location 1 column AC.

What I would want it to return is the last value for the month, currently it is adding up all value within my criteria and I couldn't figure out how to relate the date with the most recent value for that certain month.

Hope someone could help me out.

Thank you.

[AliGW]

In AC6 copied down:

=LOOKUP(2,1/((DATALIST!$B$4:$B$25<='LOCATION 1'!$AB$4:$AC$4)*(DATALIST!$C$4:$C$25='LOCATION 1'!$B6)),DATALIST!$D$4:$D$25)

[MyStix01]

Hi Ali,

Thanks, that was fast. I have a little issue though, I would still need the criteria for the location since I would have different sheets for different locations. I tried to incorporate it in your formula, however it is returning #N/A

=LOOKUP(2,1/((DATALIST!$B$4:$B$25<='LOCATION 1'!$AB$4:$AC$4)*(DATALIST!$C$4:$C$25='LOCATION 1'!$B6)*(DATALIST!D3:H3='LOCATION 1'!B2:AD2)),DATALIST!$D$4:$H$25)

[MyStix01]

Hi again,

I think it would be better to give a visual, so I think here is where the problem arises, I tried to insert Sheet 3 for Location 2, as you'd notice, the formula is not picking up any value for Watermelon though supposed to be it should be 41, I think that is since because the formula is considering that the last date for all Location is 30/06/2019, wherein that actually only applies for Location 1, Location 2's last known data is actually 23/06/2019, hence if should have been returning cell value 41.

Apologies for the follow up, I am not just quite familiar with the formula you have used. Thanks for your help Ali.

[JeteMc]

Try pasting the following into cell AC6 on the Location 2 sheet, then copying down:

Please Login or Register  to view this content.

Let us know if you have any questions.