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

ericmeister

New Member
Joined
Mar 21, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

budget-tracking.png


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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Ok, seems like IFs and INDEXesses etc were enough. Perhaps not the best way to do it but changed the @EffectiveDate function now to

Rich (BB code):
=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)
 
Upvote 0
Solution
Added one more IF anyway to check if the first item in Budget Tracking is not salary, then just use the @Date as @EffectiveDate


Excel Formula:
=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]
)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top