IF AND with combination of does not equal and does equal

melimob

Hi I have 3 columns all containing country names.

For each row, I want to do a nested statement to show:

If all 3 countries (columns) = "UAE" then put "Local Servicing - UAE"

*If all 3 countries are the same then put "Local Servicing"

*If first 2 columns are UAE but column 3 is not then put "HUB"

otherwise put "HUB & Spoke",

I had some of it working but can't get those with an asterix to work? Any ideas please?:

Below doesn't work?

=IF(LEVEL@row = "DK", IF(AND([Servicing Country]@row = "UAE", [Fulfilment Country]@row = "UAE", [Employee Country]@row = "UAE"), "LOCAL SERVICING - UAE"), IF(AND([Employee Country]@row <> "UAE", [Servicing Country]@row = "UAE", [Fulfilment Country]@row = "UAE"), "HUB"))

Brent Wilson

I am a bit confused by the logic. It is important to have it clear before you start developing the IF statement.

I don't know what First2 columns are related to [Fulfilment Country], [Employee Country],[Servicing Country] and there is no mention of LEVEL

I have looked at your logic and think I understand it this way.

It is best to rule out conditions first and then try to account for them all at the end

• If Column1 and Column2 are not the same then you always have "HUB and Spoke"
• If Column3 does not the same as Column1 (or 2) then we have "HUB"
• If Column 3 is UAE then you have "Local Servicing - UAE" otherwise "Local Servicing"

Try this

=IF(NOT(Column1@row = Column2@row),"Hub & Spoke",IF(NOT(Column1@row = Column3@row),"HUB",IF(Column3@row = "UAE","Local Servicing - UAE","Local Servicing")))

Breaking it down

First Check for Different Countries in Column1 and Column2

IF(NOT(Column1@row = Column2@row),"Hub & Spoke",

Second Check that Column3 doesn't match Column2 (We know Column1 = Column2 at this point so it is arbitrary which one we pick)

IF(NOT(Column1@row = Column3@row),"HUB",

Third Check for UAE (as we now know Col3 is the same as Col1 and Col2)

IF(Column3@row = "UAE","Local Servicing - UAE","Local Servicing")))