I am trying to make a new column that gets the spend for each Date_1yr that exists in the Date column. I have attached a screenshot of the test data that I have been using. Where the Date_1yr is minus 1 year plus one day difference from Date, adjusted during leap year. I have already written a formula that gets me the date that I want, but I can't figure out how to populate the adjusted spend. So for example, I would want the original date and spend to be matched, and the 1 year adjusted spend to be taken from the original spend column based on where the 1 year adjusted date matches the original date like this:
Date | Spend | Date_1yr | Spend_1yr |
2020-01-01 | 300 | 2019-01-02 | 250 |
2020-01-02 | 400 | 2019-01-03 | 350 |
2022-01-01 | 1,400 | 2021-01-02 | 1,200 |
I was thinking either a multi-row formula with some sort of expression would work, or a join tool. I haven't had much luck with either. Thank you in advance if you have any suggestions!
Solved! Go to Solution.
@Miles_Waller
I will do the same😁
Thank you!