How to reconcile monthly expected vs actual amounts using query and vlookup?

Question

Working on automating the categorisation of my monthly bank statement. Categorisation working well, where I am getting stuck though is reconciling each month with expected amount vs. actual.

I do have a workaround in that I use a query to return the amount for a specific month group by the category, and then I use vlookup to my expected list matching on the category and retuning the actual. Although this works, it just feels like it could be simpler. I have tried using query and adding another condition, i.e. where category on the bank statement is the same as in my list of categories used to list the expected values. As this is per month, I include a pivot on the date (year, month). That works in that it then returns the value per month using my category list to match the category from the bank statement. The issue with this is because of the pivot, if I do this for each item on my category list, the months will be repeated with the value on the next line.

The other option is to return the category group by month, and then somehow sequence the order of the returned categories and values to my list of categories.

Included link to example sheet. Open to other suggestions

https://docs.google.com/spreadsheets/d/1IqwVkYAt2c6Ng7vfv5AZNY_xR98RygqqXSDbmhVAkF4/edit?usp=sharing

Answer 1

You may try:

=index(let(Σ,unique(eomonth(torow(A2:A,1),),1),
             {text(Σ,"mmm yy");makearray(counta(E2:E),counta(Σ),lambda(r,c,sumifs(C:C,B:B,index(E2:E,r),eomonth(A:A,),index(Σ,,c))))}))

• It will expand automatically to rightwards n' downside if and when new expenses OR months are added to the bank statement raw data accommodated in Columns A:C