IF function in excel is too long. How to simplify?

Question

I have written a long If function in a Cell in an Excel 2003 worksheet.

I wish to add to it but, Excel is telling me that my function is too long.

Does anybody know how to simplify or reduce the length of the function?

In Column K3, I have a drop down list of Types of Defect, then this IF function is in column L3 to appear a specific Defect Description based on the Types of Defects selected in column K3.

=IF(ISTEXT(K3)=TRUE,IF(OR(K3="Abnormal Finishing",K3="Bending Mark",K3="Bent",K3="Contamination",K3="Crack",K3="Damage",K3="Dented",K3="Discoloration",K3="Finger Print",K3="Flow Mark",K3="Gap",K3="Insufficient Paint",K3="Scratches",K3="Rusty",K3="Stain Mark",K3="Standoff Mark",K3="Tool Mark",K3="Warpage",K3="Water Mark",K3="White Mark",K3="White Spot"),"Cosmetic",IF(OR(K3="Angle Out",K3="Dimension Out",K3="Fitting Problem"),"Dimension",IF(OR(K3="Assembly Misalignment",K3="Fan Broken",K3="Fan Not Functioning",K3="Assembly Wrong Orientation",K3="Missing Component",K3="Missing Rivet (Assembly)",K3="Part Warping (Assembly)",K3="Rivet Loose (Drop) (Assembly)",K3="Rivet Wrong Location (Assembly)",K3="Rivet Wrong Orientation (Assembly)",K3="Screw Loose (Drop)",K3="Screw Stuck"),"Assembly","ERROR"))),"ERROR")

Answer 1

A simple way would be to make separate lists and check if K3 exists in the list. For example make a list in any column (J here) for

1. Abnormal Finishing
2. Bending Mark
3. Bent
4. Contamination

use this formula to check if your value in K3 exists in this list

=IFERROR(MATCH(K3,J11:J14,0)>0,FALSE)

J11:J14 is my list. The formula results in TRUE or FALSE

Your final formula would look like

=IF(ISTEXT(K3),IF(IFERROR(MATCH(K3,L3:L7,0)>0,FALSE),"Cosmetic",IF(IFERROR(MATCH(K3,M3:M7,0)>0,FALSE),"Dimension",IF(IFERROR(MATCH(K3,N3:N7,0)>0,FALSE),"Assembly","ERROR"))),"ERROR")

where

L3:L7, M3:M7, N3:N7 are lists for Domestic, Dimension and Assembly criteria

This could be firther worked upon.

Answer 2

I usually find that multiple IF statements can be avoided using a VLOOKUP function. You need your conditions and results in a table like this:

Paste into A4:A39

Abnormal Finishing
Bending Mark
Bent
Contamination
Crack
Damage
Dented
Discoloration
Finger Print
Flow Mark
Gap
Insufficient Paint
Scratches
Rusty
Stain Mark
Standoff Mark
Tool Mark
Warpage
Water Mark
White Mark
White Spot
Angle Out
Dimension Out
Fitting Problem
Assembly Misalignment
Fan Broken
Fan Not Functioning
Assembly Wrong Orientation
Missing Component
Missing Rivet (Assembly)
Part Warping (Assembly)
Rivet Loose (Drop) (Assembly)
Rivet Wrong Location (Assembly)
Rivet Wrong Orientation (Assembly)
Screw Loose (Drop)
Screw Stuck

Paste into B4:B39

Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Cosmetic
Dimension
Dimension
Dimension
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly
Assembly

Then you can use the following formula:

=IFERROR(VLOOKUP(K3,$A$4:$B$39,2,0),"ERROR")