Hi,

Is there any way to shorten the below formula : I am not able to use index and match because it needs to refer to more than one column e.g, if its local special or locally sourced column AC needs to be checked and if its Factory Source or Factory Special column X needs to be checked:

=IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1329),AND(F1140="local Special",AC1140=$AC$1329)),"A1",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1332),AND(F1140="local Special",AC1140=$AC$1332)),"A3",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1334),AND(F1140="local Special",AC1140=$AC$1334)),"A2",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1333),AND(F1140="local Special",AC1140=$AC$1333)),"A4",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1336),AND(F1140="local Special",AC1140=$AC$1336)),"A4",IF(OR(AND(F1140="Factory Special",X1140=$X$1327),AND(F1140="Factory Sourced",X1140=$X$1327)),"AT",IF(OR(AND(F1140="Factory Special",X1140=$X$1330),AND(F1140="Factory Sourced",X1140=$X$1330)),"DB",IF(OR(AND(F1140="Factory Special",X1140=$X$1329),AND(F1140="Factory Sourced",X1140=$X$1329)),"DB",IF(OR(AND(F1140="Factory Special",X1140=$X$1328),AND(F1140="Factory Sourced",X1140=$X$1328)),"MP",IF(OR(AND(F1140="Factory Special",X1140=$X$1331),AND(F1140="Factory Sourced",X1140=$X$1331)),"YT",IF(OR(AND(F1140="Factory Special",X1140=$X$1330),AND(F1140="Factory Sourced",X1140=$X$1330)),"DB",IF(AND(F1140="Local Special",X1140=$X$1330,AC1140=$AC$1339),"DC",IF(OR(AND(F1140="Local Special",X1140=$X$1330),AND(F1140="Locally Sourced",AC1140=$AC$1328)),"DD", IF(OR(AND(F1140="Factory Special",X1140=$X$1332),AND(F1140="Factory Sourced",X1140=$X$1332)),"DB", IF(OR(AND(F1140="Factory Special",X1140=$X$1333),AND(F1140="Factory Sourced",X1140=$X$1333)),"DB", IF(OR(AND(F1140="Factory Special",X1140=$X$1334),AND(F1140="Factory Sourced",X1140=$X$1334)),"DB",IF(F1140="Factory Sourced -D","D",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1327),AND(F1140="local Special",AC1140=$AC$1327)),"DB",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1328),AND(F1140="local Special",AC1140=$AC$1328)),"DD ",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1330),AND(F1140="local Special",AC1140=$AC$1330)),"MP",IF(OR(AND(F1140="locally Sourced",AC1140=$AC$1338),AND(F1140="local Special",AC1140=$AC$1338)),"YT","")))))))))))))))))))))$1338)),"YT","")))))))))))))))))))))
Thank You in advance!!