Match one item with another based on criteria (product, date and amount).

[Specialist]

Hello guys,
I have to find a dynamic formula to match outflows with inflows based on given "Product" and "amount" and which returns the date to which that outflows was associated with the relative inflow (return = date of the inflow associated).
Therefore if some outflows perfectly matches with one inflow (outflow 1 + outflow 2 = inflow 1), the next outflow (e.g. 2nd/3rd/4th) should match with the second inflow based on the earliest date (that's the hard part because it would mean to check whether inflow 1 has still space to "contain" the next inflow, otherwise it moves to the next inflow).
At the same time the date of the date of outflow can't be earlier than the date of the inflow to be associated.
The output is the attribution date (based on the inflow associated).

I attached this excel to help you with the exlpanation

 

[Monty]

Hello Specialist

Quick solution without looking at your attachment

To dynamically match outflows with inflows based on "Product" and "Amount" and return the associated inflow date:

1. Assuming your data starts in Column A with headers:
- A: Date (Inflows)
- B: Product (Inflows)
- C: Amount (Inflows)
- D: Date (Outflows)
- E: Product (Outflows)
- F: Amount (Outflows)
- G: Attribution Date (Result)

2. In cell G2, where your result will be, enter the following formula:

=IFERROR(SMALL(IF((B$2:B$100=E2)*(C$2:C$100=F2)*(D$2:D$100<=A2),D$2:D$100,""),COUNTIF(E$2:E2,E2)),"")


3. Drag this formula down for all your rows.

This formula uses the SMALL function along with IF conditions to find the earliest matching date for each outflow based on Product and Amount. The COUNTIF function ensures that for each new inflow, the formula looks for the next earliest date.

Adjust the range (e.g., B$2:B$100) based on the size of your dataset, and ensure your data is sorted appropriately for accurate results.