Pitfalls to avoid

In constructing your formulas, it is easy to get carried away and, very soon, the formula becomes very complex and unfriendly. While it is desirable to keep formulas compact, they should be simple and easy for a third party to follow. If necessary, break up the formula into two or more parts so that it becomes easier to follow while retaining the original effect.

Alternatively, you can use Alt + Enter to force part of the formula to the next line. This will not affect the result of the formula, but it will make the formula easier to understand. Consider the following example:

=INDEX(C5:G10,MATCH(J20,C5:C10,0),MATCH(K19,C5:G5,0))

This complex formula can be broken down to three parts with the use of Alt + Enter, as follows:

=INDEX(C5:G10,
MATCH(J20,C5:C10,0),
MATCH(K19,C5:G5,0))

As we can see, this makes it easier to decipher.

...