Google Answers Logo
View Question
Q: Exceeded number of ifs allowed in Excel ( No Answer,   4 Comments )
Subject: Exceeded number of ifs allowed in Excel
Category: Computers > Algorithms
Asked by: memccaffrey-ga
List Price: $2.00
Posted: 09 May 2006 07:52 PDT
Expires: 08 Jun 2006 07:52 PDT
Question ID: 726928
Just below is my formula. Unfortunately I've exceeded the number of
if's that Excel allows.

Is there a way to rewrite this instead? 


Request for Question Clarification by maniac-ga on 09 May 2006 19:22 PDT
Hello Memcaffrey,

I see a few alternatives. Please respond with an indication of which
method you'd like to pursue.

[1] An excel macro (function) could handle this easily and could be
extended for more possible values.

[2] If you have space on the spreadsheet to add a row (perhaps hidden)
to compute the result in each if function and then use lookup() [plus
some additional conditions] to provide the result. This could also be
extended for more possible values.

There is no answer at this time.

Subject: Re: Exceeded number of ifs allowed in Excel
From: jack_of_few_trades-ga on 09 May 2006 11:41 PDT
I have run into a similar problem before.  My solution was to put part
of the equation in another cell and reference that cell for
information in your long (yet slightly shorter) list of IFs.  It's not
as easy or pretty, but it can get the job done.
Subject: Re: Exceeded number of ifs allowed in Excel
From: ansel001-ga on 11 May 2006 17:53 PDT
In your particular case, you can lesson the number of nested if
statments by using a couple more OR's in your formula.

The formula is the same if D3 = K14, J14, or I14.  Use one OR statment
instead of three nested IF's.

The formula is the same if D3 = G14 or H14.  Use one OR statment
instead of two nested IF's.
Subject: Re: Exceeded number of ifs allowed in Excel
From: mfripp-ga on 14 May 2006 15:48 PDT
If the values in cells D14:L14 are unique, you can use either of these expressions:

=CHOOSE(IF(ISNA(MATCH($D$3, D14:L14, 0)), 0, MATCH($D$3, D14:L14,
0))+1, "0", E5, E5, $D$5*0.15+K19, $D$5*0.1+K19, $D$5*0.1+K19,
$D$5*0.2+K19, $D$5*0.2+K19, $D$5*0.2+K19, $D$5*0.05)

=IF($D$3=L14,$D$5*0.05,0) + IF($D$3=K14,$D$5*0.2+K19,0) +
IF($D$3=J14,$D$5*0.2+K19,0) + IF($D$3=I14,$D$5*0.2+K19,0) +
IF($D$3=H14,$D$5*0.1+K19,0) + IF($D$3=G14,$D$5*0.1+K19,0) +
IF($D$3=F14,$D$5*0.15+K19,0) + IF(OR($D$3=E14,$D$3=D14),E5,0)

The second method is more transparent than the first method, and can
be applied to an arbitrary set of lookup cells. But it will definitely
not work if any of the cells you are searching have the same values.

The first method will work with non-unique values, but it will do the
calculation for the leftmost matching cell, instead of the rightmost
cell, which your original formula would have done (e.g., if D3 matches
both K14 and J14, my technique will use the formula for K14, and yours
would use the formula for J14).
Subject: Re: Exceeded number of ifs allowed in Excel
From: mfripp-ga on 14 May 2006 16:06 PDT
Here's a hybrid version that will work exactly like your original
expression, but without running into any limits with nested if()
functions. This will give priority to the last matching value in the

=CHOOSE(INT(LOG10(10^1+IF($D$3=D14, 10^2, 0)+IF($D$3=E14, 10^3,
0)+IF($D$3=F14, 10^4, 0)+IF($D$3=G14, 10^5, 0)+IF($D$3=H14, 10^6,
0)+IF($D$3=I14, 10^7, 0)+IF($D$3=J14, 10^8, 0)+IF($D$3=K14, 10^9,
0)+IF($D$3=L14, 10^10, 0))), "0", E5, E5, $D$5*0.15+K19, $D$5*0.1+K19,
$D$5*0.1+K19, $D$5*0.2+K19, $D$5*0.2+K19, $D$5*0.2+K19, $D$5*0.05)

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy