IF FORMULA, MULTIPLE CONDITIONS WITH TABLE RANGE AND NAMED RANGE

[malle7]

Hello!! I really need you guidance with the following.

In Sheet1 I have a Table Range
In Sheet2 I have Each Column a named range (Name Range: Plant, Name Range: Inco1a, Name Range: Inco1b)

In the image Attached Capture1
Column named "wrong" I have the following formula:
=IF(AND([@Plant]=Plant), OR(ISNUMBER(SEARCH(Inco2a,[@[Inco 2]])),ISNUMBER(SEARCH(Inco2b,[@[Inco 2]])),"ok"),"check")

Column named "right" I have the following formula"
=IF(AND(OR(ISNUMBER(SEARCH("AUBET",[@[Inco 2]])),ISNUMBER(SEARCH("BERR",[@[Inco 2]]))), OR([@Plant]="AAU",[@Plant]="AUU")),"OK",
IF(AND(OR(ISNUMBER(SEARCH("MANCHES",[@[Inco 2]])),ISNUMBER(SEARCH("CARLING",[@[Inco 2]]))), [@Plant]="CTN"),"OK",
IF(AND(OR(ISNUMBER(SEARCH("WESSE",[@[Inco 2]])),ISNUMBER(SEARCH("KOLN",[@[Inco 2]]))), [@Plant]="KLO"),"OK",
IF(AND(ISNUMBER(SEARCH("FRANK", [@[Inco 2]])),[@Plant]="FRN"),"OK",
IF(AND(ISNUMBER(SEARCH("BERL",[@[Inco 2]])),[@Plant]="BEE"),"OK",
IF(AND(ISNUMBER(SEARCH("ANTWE",[@[Inco 2]])),[@Plant]="BEL"),"OK",
IF(AND(ISNUMBER(SEARCH("PLOCK",[@[Inco 2]])),[@Plant]="PLO"),"OK",
"CHECK")))))))

The formula in "right" column gives me the results I want.
Question: How can I make the second formula shorter, using the name ranges or the data in Sheet 2?

THANKING YOU IN ADVANCE FOR YOUR SUPPORT!


Sincerely,


Malle