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")
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))))