How to use multiple formulas to know if something matches and return a statement, but if it doesn't another formula runs

Question

Sample google-sheet.

i am trying to make a formula by using match function but it does not work. Refer to the my sample data in above link, can u help me to make it works ? The formula i need is to search customer name with multiple condition and return few statement.

• If customer in column A match with the list in column E, return statement "DRAWING CHECKLIST FOR ALL ITEMS".
• If customer in column A match with the list in column F, return statement "REQUIRED CHECKLIST FOR ALL ITEMS".
• If customer in column A match with the list in column C, return statement "NO CHECKLIST REQUIRED".
• If customer in column A match with the list in column D and contains "NEW" in Column B, return statement "REQUIRED CHECKLIST FOR NEW ITEM & REVISION ONLY".
• If customer in column A match with the list in column D and do not contains "NEW" in Column B, return statement "NO CHECKLIST REQUIRED". .

I'VE TRY BELOW FORMULA, BUT NOT WORKS

=IFS(MATCH(A2,E:E,0),"DRAWING CHECKLIST FOR ALL ITEMS",MATCH(A2,F:F,0),"REQUIRED CHECKLIST FOR ALL ITEMS",AND(MATCH(A2,D:D,0),B:B="NEW"),"REQUIRED CHECKLIST FOR NEW ITEM & REVISION ONLY",AND(OR(MATCH(A2,D:D,0),MATCH(A2,D:D,0)),L:L<>"NEW"),"NO CHECKLIST REQUIRED")

Answer 1

logic would be:

but we can optimize it like:

=INDEX(LET(x, TRIM(A2:INDEX(A:A, MATCH(, 0/(A:A>"")))),  
 IF(MAP(x,  LAMBDA(a, IFNA(MATCH(a, TRIM(E2:E), ))))>0, "DRAWING CHECKLIST FOR ALL ITEMS", 
 IF(MAP(x,  LAMBDA(a, IFNA(MATCH(a, TRIM(F2:F), ))))>0, "REQUIRED CHECKLIST FOR ALL ITEMS",  
 IF((MAP(x, LAMBDA(a, IFNA(MATCH(a, TRIM(D2:D), ))))>0)*(TRIM(B2:B)="NEW"), "REQUIRED CHECKLIST FOR NEW ITEM & REVISION ONLY", 
 IF(MAP(x,  LAMBDA(a, IFNA(MATCH(a, TRIM({C2:C; D2:D}), ))))>0, "NO CHECKLIST REQUIRED", ))))))

Answer 2

Try the following formula-

=LET(x,MAX(INDEX((A2=C2:F)*(COLUMN(C2:F)))),IF(AND(x=4,XLOOKUP(A2,D:D,B:B,"")="NEW"),"REQUIRED CHECKLIST FOR NEW ITEM & REVISION ONLY",IF(AND(x=4,XLOOKUP(A2,D:D,B:B,"")<>"NEW"),"NO CHECKLIST REQUIRED",INDEX(1:1,1,x))))

Edit: This shorter version also should work.

=LET(x,MAX(INDEX((A2=C:F)*(COLUMN(C:F)))),IF(AND(x=4,XLOOKUP(A2,D:D,B:B,"")<>"NEW"),"NO CHECKLIST REQUIRED",INDEX($1:$1,1,x)))`