Multiple IF functions with multiple possible solutions

Question

OK. So I am setting up a business which sells 7 different plans based on affordability and circumstances.

I wish to input customer information into an Excel spreadsheet and the spreadsheet to tell me the Plan.

However basic IF equations are not working.

So for eg. If Customer has credit rating of more than 550, 0 arrears, wants cover of more than £20000 but equal to £25000 or less and has 2 or fewer insurance policies then = Plan 7.

=IF(AND(A1>550,B1>0,C1>20000,C1<=25000,D1<=2),"7")

(this bit is fine as it tells me "PLAN 7").

However.

If Customer has credit rating of less than 550 but more than 500, 0 arrears, wants cover of more than £15000 but £20000 or less and has 2 or fewer insurance policies then = Plan 6.

This equation just returns, "FALSE" if I put those matching variables in the appropriate cells: as in:

=IF(AND(A1>550,B1>0,C1>20000,C1<=25000,D1<=2)"7",IF(AND(A1>500,A1<550,B1>0,C1>15000,C1<=20000,D1<=2),"6"))

= FALSE (if information in spreadsheet should return PLAN 6) but continues to give me PLAN 7 if information in spreadsheet shoukd return PLAN 7.

I obviously wish to continue this with the 5 other parameters, until "PLAN 1".

What am I doing wrong?

Thanks so much. At wits end.

Answer 1

Instead of having a long complicated IF statement with hardcoded values, I instead highly recommend creating a lookup table, something like this (you can keep expanding this until you have all of your plan criteria covered for all of your plan numbers):

And then let's pretend your customer table looks like this:

Then use a SUMIFS formula to get the plan number you're looking for (important to note that we can only use SUMIFS because we are returning a number and not text). Using this example data, put this formula in cell F2 and copy down:

=SUMIFS(P:P,H:H,"<="&B2,I:I,">="&B2,J:J,"<="&C2,K:K,">="&C2,L:L,"<="&D2,M:M,">="&D2,N:N,"<="&E2,O:O,">="&E2)

That will give us the correct plan # based on the customer data:

This will make your plan criteria easier to maintain and update instead of being hardcoded in your formula, and it will make future maintenance easier and more understandable.