Condition based formula (needs some modification)

[arifanmol]

Plz check attached sheet and respond in listed itmes, there must be NO against item no. 5 as it contains neither cable or tray but it shows blank =IF(AND(ISNUMBER(SEARCH("cable",B3,1)),ISNUMBER(SEARCH("Tray",B3,1))),"No",IF(ISNUMBER(SEARCH("cable",B3,1)),"Meter","")) was applied

 

[pecoflyer]

Item 5 would only be "no" if "cable" and "tray" were present. None are

 

[bosco_yip]

Try,

[E3]: =IF(B3<>"",IF(ISNUMBER(SEARCH("cable",B3))+ISNUMBER(SEARCH("tray",B3))=1,"Meter","No"),"")

 

[arifanmol]

yes effective and useful thanks a lot