XLOOKUP or similar to get multi-value results for multiple conditions from one table

[ericmeister]

I followed "The Office Lab" guy's instructions to build a Personal Budget

I now have a table "Budget Tracking" with cols @Date, @Type, @Category, @Amount & @EffectiveDate and tracking data for multiple years. The table has a feature which enables user to automatically shift late month income to the beginning of next month (beginning at 01:27:00 in the video). When you receive your salary at the end of the month, you consider it for disposable income for the next month.

This is easy to do, you just estimate the earliest day you'll get your salary (@Type=income, @Category=salary) is 25th each month and then just convert the value in @Date column to 1st of next month in @EffectiveDate column.

But... if I get the salary like 29th, I will spend a lot from it between 29th-31st, like paying the next months bills in advance, so I'd like also to transfer these expenses. I can remove the @Type="Income" condition but then also expenses before the salary might get transfered. In below images, I have set the late income date as 25, and now also row 18 gets an @EffectiveDate of 1-Feb-23. So now I'd need to exact date when the salary is paid, not only estimate. I have that information in the table, but I just can't get my head around how to use it in Excel.

So the question is... what kind of XLOOKUP, INDEX/MATCH, TEXT or similar function I need to find a salary for each month/year, set the effective date to next month for salary AND dynamically for every transaction AFTER salary until the beginning of next month?

 

[ericmeister]

Ok, seems like IFs and INDEXesses etc were enough. Perhaps not the best way to do it but changed the @EffectiveDate function now to

=IF(shift_late_income_status="Active";
    IF([@Category]="Salary";INDEX(DATE(YEAR($C:$C);MONTH($C:$C)+1;1);ROW());
      IF(MONTH([@Date])=MONTH(INDIRECT(ADDRESS(ROW()-1;COLUMN())));[@Date];INDIRECT(ADDRESS(ROW()-1;COLUMN()))
    )
  );[@Date]
)

Seems to work well enough.

1. Check if late income feature is enabled.
1a. If late income feature is not enabled, then always use @Date from current row in @EffectiveDate column
1b. If late income feature is enabled, check if @Category is salary in current row.
2a. If @Category=salary, transfer the @Date from current row in 1st day of next month in @EffectiveDate column
2b. If @Category!=salary, compare the month number of @Date in current row to month number of previous row of @EffectiveDate.
3a. If it matches, stop using date shifting and use regular @Date from current row in @EffectiveDate column
3b. If it does not match, use the @EffectiveDate from previous row in @EffectiveDate column.

When shift_late_income_status is enabled, it is now assuming the first row is salary in Budget Tracking (which it of course should be on 1st year, transferring saldo from previous year)

 

[ericmeister]

Added one more IF anyway to check if the first item in Budget Tracking is not salary, then just use the @Date as @EffectiveDate

=IF(shift_late_income_status="Active";
    IF(AND(ROW()=tracking_min_row;[@Category]<>"Salary");[@Date];
      IF([@Category]="Salary";INDEX(DATE(YEAR($C:$C);MONTH($C:$C)+1;1);ROW());
        IF(MONTH([@Date])=MONTH(INDIRECT(ADDRESS(ROW()-1;COLUMN())));[@Date];INDIRECT(ADDRESS(ROW()-1;COLUMN()))
       )
    )
  );[@Date]
)