Alternative to many nested IFs
When you require a long series of nested IFs, there is another approach that does not restrict you to the nested IF limit. It uses a "selection" approach, which can also be easier to create because it is visually more straightforward.
Nested IFs evaluate conditions consecutively until it finds one that is true. The logic and the output values are an intermixed series. The alternative approach evaluates all of the conditions in one step and uses the result to select the right value from a list.
It combines two techniques, the CHOOSE function and a Boolean expression. In Excel 2003, you could CHOOSE from up to 29 values. Excel 2007 and later versions allow up to 254 selections, without using any IF statements.
The CHOOSE Function
You have a long formula so I won't reproduce the entire thing, but here's the approach (the placeholder "index" is explained in the next part):
Your whole list of IF statement result values gets included. The way this works is the "index" is calculated from all of the conditions that are part of your nested IF chain. The result will be the sequence number of the value in the CHOOSE list.
The index uses Boolean arithmetic (calculations based on 0/1 values of True/False conditions). You build the index as an expression like this:
1 + condition1 * 1 + condition2 * 2 + condition3 * 3 + ...
The initial 1 will be explained in a minute. The rest is all of your IF test conditions in the associated order to match the results list. Each test evaluates to a 1 or 0, which then gets multiplied by its associated index number (the 1, 2, 3, ... following each condition). Since only one of these tests will be true, that determines the value of the index (a sum of zeros for all false conditions plus an index value for the True one).
Substituting the test conditions from your nested IF example would look like this:
1 + (D2=1)*1 + AND(D2=2,D3="I")*2 + AND(D2=2,D3="II")*3 + ...
The entire expression goes in place of the
<index> placeholder in the formula shown under the CHOOSE Function heading. The Choose function then selects the target value from the list based on the index.
Your error condition is if none of the tests are true, which would evaluate to a sum of zero. The index is the position number of the value in the list, which starts with 1. Adding 1 to the index calculation (shown as the first term), makes the error condition evaluate to 1 and increases all of the other results by 1. So the first result in the list is your error message.