1

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.

Screenshot
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.

two lookup tables

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.

1 Answer 1

0

I assume that you’re talking about Row 17.  Your formula says, if Column L = 1 (L17 is 1) and 10−Column I <= 6 (i.e., Column I4; and I17 is 4), then we should lookup the value from Column F (F17 is $50) in the second table.  Well, $50 is > $30 (Conditions!$E$18; i.e., Hurdle 18) and < $71 (Hurdle 19), so you get List Type 18 (Conditions!$A$18), which is DO4.

3
  • Hi Scott, thanks for the answer. He must get DO4 after 6 months. But here he received DO4 status in first month. Which is wrong, only after a no donation a user can technically enter DO list. Hope this clears. After the initial donation, he must be in 4A.
    – Hari
    Nov 30, 2015 at 5:16
  • Are you expecting me to fix your formula for you?  I don't feel like it; I've explained what the formula is doing; now you should try to fix it.  If you get stuck, try to explain in clear English what it is supposed to do, and what the other columns in the spreadsheet mean.  Note that your formula currently gets a 4A result only if Column I < 4 and Column L = 1, which never happens in your spreadsheet. Nov 30, 2015 at 5:27
  • No I created the formula above, I know how it works. But I don't knnow how to get 4A there instead of DO4. SO basically, the DM column should decide, if that is the first ever donation. basically kind of an if condition somewhere. But not sure how to write it. I tried different methods, it didn't work.
    – Hari
    Nov 30, 2015 at 5:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .