Fetching information from multiple columns based on value on the same row in Google Sheets

Question

I'm not very experienced with Google Sheets' functions and formulas, but I'm starting to understand the logic.

I'm currently completely stuck with trying to come up with a weekly based Liquidity sheet that gathers information from multiple tabs and places the sums on the right cell based on the expense name in column A, and the date & week & year in the upper rows (12+ months):

Column A	Column B	Column A	Column B
Name	Week 1	Week 2	Week 3
Expense1	(the formula), €?	?€	?€
Expense2	?€	?€	?€
Expense3	?€	?€	?€

I have figured out solutions for most of the cases, but the problem is with the recurring costs.

Recurring costs are listed as follows:

Column A	Column B	Column A	Column B
Name	Amount	Date	Date
Expense1	€	next invoice date	invoice date + X months
Expense2	€	next invoice date	invoice date + X months
Expense3	€	next invoice date	invoice date + X months

The sequence of dates continue to the right (still a work in progress how it will work automatically based on multiple conditions)

I've tried all kinds of different solutions to solve one problem at a time, but it's really hard for me to come up with a formula that would "do it all".

I tried Xlookup for fetching the upcoming payment dates but ran into the problem with Arrayformula that it only returns the information of the first column. With Filter I get all the columns, from all the rows..

I've also tried Query, Lambda, Byrow and now thinking if it should be done with Map.

Just in case, I made a super simple example sheet: https://docs.google.com/spreadsheets/d/18sX800vqc4YsijSvzEfOf27M16uUkrD9bibYIvFrIew/edit?usp=sharing

The real one of course includes multiple tabs for different expense categories and income etc.

Any help is appreciated! (:

Answer 1

Here's one possible approach you may test out & improvise on:

=map(A5:index(A:A,match(,0/(A:A<>""))),lambda(Σ,if(Σ="",,index(if(ifna(xmatch(B4:4,xlookup(torow(xlookup(Σ,'Recurring Costs'!A:A,'Recurring Costs'!C:ZZ),1),4:4,4:4,,-1))),vlookup(Σ,'Recurring Costs'!A:B,2,),)))))