a lookup formular in convert my daily data into monthly data

[uqls]

Hi All,

I want to build a lookup formular in convert my daily data into monthly data.

There is a tricky part is here: some of my data does not start to have value on the first couple days of the month, but i want my lookup table to reflect a value of any day in the month

For example, In colume A, my data starts with 0 for the first of day in may 2026, and then it begins to have value. I want my lookup table in group B to reflect the cell with number on May 2026.


Many Thank

[AliGW]

Are you still using Excel 2013?

In F3 copied down:

=SUMIFS($B$3:$B$336,$A$3:$A$336,">="&E3,$A$3:$A$336,"<"&EDATE(E3,1))

[uqls]

H Ali,

Thank you. But it is a monthly sum. I would like it to be a match between my daily data with my monthly data.

Hope you can help. Many thank

[AliGW]

OK. This is why you should always show expected results in your sample workbook!

Are you still using Excel 2013?

Try this:

=MAX(IF($A$3:$A$336>=E3,IF($A$3:$A$336<EDATE(E3,1),$B$3:$B$336)))

Array formulae are not entered in the same way as 'standard' formulae. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[uqls]

Hi Ali,

Thank you. It works.

Have a wonderful day

Regards,

[AliGW]

Could you please answer my question? Are you still using Excel 2013???

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks for the rep.

[Czeslaw]

Power Query

Please Login or Register  to view this content.