Apr 08 2019 02:26 AM
I am trying to do an =IF formula with multiple logical options: =IF(OR(A5=CRM!A5,"CRM",[A5=Affiliates!A5,"Affiliate"],[A5=Supplier!A5,"Supplier]) But it is not accepting and I can't see where I'm going wrong.
Apr 08 2019 02:35 AM
@Annalee2130 , what is the logic for OR in plain English? Perhaps you need nested IF like
=IF(A5=CRM!A5,"CRM",IF(A5=Affiliates!A5,"Affiliate",IF(A5=Supplier!A5,"Supplier","nothing from it"))))
Apr 08 2019 02:44 AM
In plain English:
If Cell "ContactDetails"A5 is equal to Sheet "CRM"A5 then cell should fill as "CRM"
If Cell "ContactDetails"A5 is equal to Sheet "Affiliates"A5 then cell should fill as "Afilliates"
and
If Cell "ContactDetails"A5 is equal to Sheet "Suppliers"A5 then cell should fill as "Suppliers"
Apr 08 2019 02:48 AM
Sorry - only saw your suggested formula after I replied - let me try this....
Apr 08 2019 03:33 AM
@Annalee2130 , it checks one by one and returns the value for first match
Apr 08 2019 04:21 AM - edited Apr 08 2019 04:23 AM
I am not sure what the square brackets were intended to be? An alternative to a nested IF that is available from Excel 2016 and on is the IFS function.
= IFS(
A5=CRM!A5, "CRM",
A5=Affiliates!A5, "Affiliate",
A5=Supplier!A5, "Supplier" )
Apr 08 2019 05:04 AM
@Peter Bartholomew , for IFS I'd add TRUE condition at the end if no one match