EDIT: SOLUTION BELOW
An example will make it easier to understand. The idea is very simple, at least so it seems.
With the application of dynamic lookup values I want to match values of one column to values of another and list the individual results. If due to the dynamic range of the lookup value space is limited I can 'sum' the output in one cell in case of several matches.
It is also possible to add the desired amount of rows instead to accommodate for any possible additional matches, but I haven't been able to figure out a formula to accomplish that yet.
All columns, data and hence lookup values are dynamic.
Problem areas Highlighted:
The following formulas I have right now (drag down to cells below):
EDIT: FOUND ACCEPTABLE SOLUTION
So I managed to accomplish what I wanted in a fairly easy way with additional 'helper' columns. I decided to accept that the budget column shall only show the SUM of all budget entries on a given day. Then in order to add one empty row to make space for a date on which there was a budget entry, but on which no purchase has been made ('receipt'), without disturbing the flowing dynamic range of both the date and receipt column, I used the following setup with reference to the example screenshot:
Condition: A receipt entry never shares the same row as a budget entry
1) Helper column #1 replaces each budget entry in column D with a unique value that can be used for lookup.
2) Helper column #2 each cell displays its row number.
3) Helper column #3 copies the exact matching dates to all receipt values of column C on their respective row numbers.
4) Helper column #4 does the same as above with the difference that all blanks are replaced by the dates on which budget entries have been made.
5) Helper column #5 combines helper column #3 and #4 by only adding unique budget dates in the blank spaces that are not present in column #3
6) DESIRED OUTPUT for the Date column (column I) is formed by removing all blank spaces from helper column #5
7) DESIRED OUTPUT for the Receipt column (column J) is formed by following the same procedure with helper columns in order to add the desired extra row. This is achieved by adding another helper column #6 replacing the date that is added on a row with a blank cell in helper column #4 with a zero value. This way a distinction is made between the added "zero value" cell and the other blank cells. By removing all blank cells in the desired output column J the zero value will create the extra desired row without disturbing the receipt entries and their corresponding dates.
8) DESIRED OUTPUT for the Budget column (column K), now, simply shows the sum of all budget entries on a specific date by adding this value on the first row of each new instance of a corresponding date in column I.
See all formulae below:
All formulae below are put on the first line of each respective column that can be copied down. For this example I use column AA for helper column #1, AB for #2, and so on:
Helper column #1 - AA
Helper column #2 - AB
Helper column #3 - AC
Helper column #4 - AD
Helper column #5 - AE
Helper column #6 - AF
DESIRED OUTPUT COLUMN I
DESIRED OUTPUT COLUMN J
DESIRED OUTPUT COLUMN K
Not sure if this, by adding 6 (!) additional helper columns, is the most efficient way, but it does the job. It would be great to have an array formula combine all of this, but I don't know if that would be possible. Any working suggestions for better alternatives are welcome.