Why is my OR Code Failing

[Excel_77]

Hi,

Can anyone correct this? My code is not quite working and it appears to be a problem with my OR sections, can anyone correct this?

=IF(AND(AZ2="ID",ISNUMBER(SEARCH("Com [L5]",AL2))=FALSE),"Investment",IF(OR(AZ2="Global Consumer",AL2="Commercial too"),"Retail ",IF(AND(AK2="Activities [L4]",ISNUMBER(SEARCH("Audit [L5]",OR("Risk",OR("Independent")),AL2))=FALSE),"Corporate",IF(AND(AK2="Activities [L4]",ISNUMBER(SEARCH("Audit [L5]",OR("Risk",OR("Independent")),AL2))=TRUE),"Independent"))))

 

[gmhumphr]

The issue is within your 2nd and 3rd AND statements where you use the SEARCH function. As written they are invalid (doesn't inherently allow for multiple search parameters). Replace the ISNUMBER(SEARCH) portions with SUMPRODUCT(--ISNUMBER(SEARCH({"Audit[L5]","Risk","Independent"},AL2)))>0 [this will return TRUE or FALSE and can be paired with your existing logic check in those areas].

Reference this page if you want more on the topic: https://exceljet.net/formula/cell-contains-one-of-many-things