I have a big dataset, dataset is in such a way that each time a customer is called a row will be created. So the customer Number is the unique ID here. Based on their current and past response type, users are classified.
First donation: 4A
Second donation in less than three or five months, then goes to first lookup table.
No donation for 6 months after first donation, the second lookup table.
How to compute this in the following dataset? Also the lookup table here is split into two to specify conditions. I am using index and match to locate the lookup.
The problem is, for instance customer number 5513 has donated first time on 4th month and he has donated, then he must end up in list 4A, but he is in list DO4. Because I have used “10-$I2<=6” in the formula before second index function.
So in an ideal case, for each customer, the function should go back and check when was the last donation made. If there was no donation made before and he has donated in the present then he ends up in list 4A.
For example, 5513 first row is on 4th month (Action month), so it should check for 5513 history. There is no donation records, so the 4th month outcome is donation so it must be list 4A. If he has donated before, then if it was donated less then four months ago, then based on his current donation amount lookup table will provide a list type. As per the conditions in first table. But if 5513 is not donating this time, and if there was a donation 6 months ago then he must go into second lookup table to categorize according to the money he donated last time. If there is no history of donation and he hasn’t donated this time as well then he will be in the "No Donation" state.
Dataset sample (Click image to enlarge)
The following is the conditions, the first table is if the user has donated more then two times. If he has donated only once then he needs to be looked up in second table.
The formula I am using to lookup the table is:
=IF(E2=F2, IF(L2>=2, INDEX(Conditions!$A$3:$E$7, MATCH(F2,Conditions!$E$3:$E$7,1),1), IF($L2=1,IF(10-$I2<=6, INDEX(Conditions!$A$16:$E$20, MATCH(F2, Conditions!$E$16:$E$20, 1), 1), Conditions!$A$11), Conditions!$A$16)), "Received Amount Differs")
I could have a breakdown for each month.