I've been asked to add two new additional criteria to the formula below.
=IFERROR(IF((ISNUMBER(MATCH(D2,'GOSC Docs'!$A$2:$A$16,0))*(D2<>"SMILE_ERR")+(D2="SMILE_ERR")*(O2="B07")+(D2="SMILE_ERR")*(O2="B36"))*(AK2="GOSC")=1,IF(OR(P2="",P2="STR"),VLOOKUP(AF2,'GOSC SM'!$C$2:$H$32,6,0),IFERROR(VLOOKUP(P2,'GOSC RM'!B:F,5,0),VLOOKUP(AF2,'GOSC SM'!$C$2:$H$32,6,0))),""),"")
New CRITERIA:
1. If column D = "Refund" OR if column W = "Y", then the formula should provide a blank assignment.
2. In the event that column P is populated with initials that aren’t recognized by the lookup to the "GOSC RM" tab AND column AL contains either "AD-TPA", "AD-Assoc" or "AD-PEO", could we have the formula populate the cell with “SPM”?
Would someone be able to provide me the modified formula?
=IFERROR(IF((ISNUMBER(MATCH(D2,'GOSC Docs'!$A$2:$A$16,0))*(D2<>"SMILE_ERR")+(D2="SMILE_ERR")*(O2="B07")+(D2="SMILE_ERR")*(O2="B36"))*(AK2="GOSC")=1,IF(OR(P2="",P2="STR"),VLOOKUP(AF2,'GOSC SM'!$C$2:$H$32,6,0),IFERROR(VLOOKUP(P2,'GOSC RM'!B:F,5,0),VLOOKUP(AF2,'GOSC SM'!$C$2:$H$32,6,0))),""),"")
New CRITERIA:
1. If column D = "Refund" OR if column W = "Y", then the formula should provide a blank assignment.
2. In the event that column P is populated with initials that aren’t recognized by the lookup to the "GOSC RM" tab AND column AL contains either "AD-TPA", "AD-Assoc" or "AD-PEO", could we have the formula populate the cell with “SPM”?
Would someone be able to provide me the modified formula?
