IF(CONTAINS) statement applied to cells with multiple entries; desire mulitple output

K M

I am writing a formula to read a cell that contains city names, and I want to output a general region/area.

In order to do this, I have written an "IF" formula that uses the "CONTAINS" function so it doesn't have to be an exact match. But I also want the output to be dynamic... so if the cell contains say Paris and Hong Kong, the formula would output TWO regions rather than the first condition that is met.

Today it looks like this:

=IF((ISBLANK([Site Impact]1)), "None", IF(CONTAINS("Sydney", [Site Impact]1), "Australia", IF(CONTAINS("Hong Kong", [Site Impact]1), "East Asia", IF(CONTAINS("Shanghai", [Site Impact]1), "East Asia", IF(CONTAINS("Tokyo", [Site Impact]1), "East Asia", IF(CONTAINS("Hong Kong", [Site Impact]1), "East Asia", IF(CONTAINS("Berlin", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Dublin", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Dundalk", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("London", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Luxembourg", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Moscow", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Paris", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Tel Aviv", [Site Impact]1), "Europe, Middle East and Africa", IF(CONTAINS("Guatemala", [Site Impact]1), "Latin America", IF(CONTAINS("Sao Paolo", [Site Impact]1), "Latin America", IF(CONTAINS("Chandler", [Site Impact]1), "North America", IF(CONTAINS("Chicago", [Site Impact]1), "North America", IF(CONTAINS("New York", [Site Impact]1), "North America", IF(CONTAINS("San Francisco", [Site Impact]1), "North America", IF(CONTAINS("San Jose", [Site Impact]1), "North America", IF(CONTAINS("Vancouver", [Site Impact]1), "North America", IF(CONTAINS("Bengaluru", [Site Impact]1), "Southeast Asia", IF(CONTAINS("Chennai", [Site Impact]1), "Southeast Asia")))))))))))))))))))))

It outputs the first condition met only. I'm interested in checking against ALL conditions. Do I have to write additional "IF" conditions that combine every permutation of combinations to achieve this or is there an easier way? Trying to save myself eye strain.

Genevieve P.

Hi @K M

Instead of Nesting the IF statements together, you could have the first ISBLANK statement nested, but then add all the other IF Statements together with a + Symbol, and have each location's false part of the statement be "blank".

So, for example:

=IF((ISBLANK([Site Impact]@row)), "None", (IF(CONTAINS("Sydney", [Site Impact]@row), "Australia", "") + IF(CONTAINS("Hong Kong", [Site Impact]@row), "East Asia", "")))

Notice how I added a "" after the country? This will then make sure not to output anything if it doesn't contain Sydney. I also used @row instead of a row number (in your case, 1) to make sure the formula is able to process as fast as possible. Using @row tells the formula it only needs to scan through the cell in this row for the possible data, instead of needing to also search the sheet for what number to look into.

Now, I recognize this will squash together your multiple outputs together in one word/line, so you may also want to add in some sort of indicator when there's a new country. In my case, I've added a space and a - symbol before hand, so when they appear together the output looks more manageable in a cell:

=IF((ISBLANK([Site Impact]@row)), "None", (IF(CONTAINS("Sydney", [Site Impact]@row), " -Australia", "") + IF(CONTAINS("Hong Kong", [Site Impact]@row), " -East Asia", "")))

Finally, I notice that many of your outputs are actually the same thing... we can make this easier to read by combining all possible options into an OR statement for the same output. This way you won't have a double output of "North America" if both "Vancouver" and "Chicago" are picked (does that make sense?).

FINAL FORMULA broken down:

=IF((ISBLANK([Site Impact]@row)), "None", 

(IF(CONTAINS("Sydney", [Site Impact]@row), " -Australia", "") 

+ 

IF(OR(CONTAINS("Hong Kong", [Site Impact]@row), CONTAINS("Shanghai", [Site Impact]@row), CONTAINS("Tokyo", [Site Impact]@row)), " -East Asia", "")

+ 

IF(OR(CONTAINS("Berlin", [Site Impact]@row), CONTAINS("Dublin", [Site Impact]@row), CONTAINS("Dundalk", [Site Impact]@row), CONTAINS("London", [Site Impact]@row), CONTAINS("Luxembourg", [Site Impact]@row), CONTAINS("Moscow", [Site Impact]@row), CONTAINS("Paris", [Site Impact]@row), CONTAINS("Tel Aviv", [Site Impact]@row)), " -Europe, Middle East and Africa", "")

+

IF(OR(CONTAINS("Guatemala", [Site Impact]@row), CONTAINS("Sao Paolo", [Site Impact]@row)), " -Latin America", "") 

+

IF(OR(CONTAINS("Chandler", [Site Impact]@row), CONTAINS("Chicago", [Site Impact]@row), CONTAINS("New York", [Site Impact]@row), CONTAINS("San Francisco", [Site Impact]@row), CONTAINS("San Jose", [Site Impact]@row), CONTAINS("Vancouver", [Site Impact]@row)), " -North America", "")

+

IF(OR(CONTAINS("Bengaluru", [Site Impact]@row), CONTAINS("Chennai", [Site Impact]@row)), " -Southeast Asia", ""

FULL FORMULA, ALL TOGETHER:

=IF((ISBLANK([Site Impact]@row)), "None", (IF(CONTAINS("Sydney", [Site Impact]@row), " -Australia", "") + IF(OR(CONTAINS("Hong Kong", [Site Impact]@row), CONTAINS("Shanghai", [Site Impact]@row), CONTAINS("Tokyo", [Site Impact]@row)), " -East Asia", "") + IF(OR(CONTAINS("Berlin", [Site Impact]@row), CONTAINS("Dublin", [Site Impact]@row), CONTAINS("Dundalk", [Site Impact]@row), CONTAINS("London", [Site Impact]@row), CONTAINS("Luxembourg", [Site Impact]@row), CONTAINS("Moscow", [Site Impact]@row), CONTAINS("Paris", [Site Impact]@row), CONTAINS("Tel Aviv", [Site Impact]@row)), " -Europe, Middle East and Africa", "") + IF(OR(CONTAINS("Guatemala", [Site Impact]@row), CONTAINS("Sao Paolo", [Site Impact]@row)), " -Latin America", "") + IF(OR(CONTAINS("Chandler", [Site Impact]@row), CONTAINS("Chicago", [Site Impact]@row), CONTAINS("New York", [Site Impact]@row), CONTAINS("San Francisco", [Site Impact]@row), CONTAINS("San Jose", [Site Impact]@row), CONTAINS("Vancouver", [Site Impact]@row)), " -North America", "") + IF(OR(CONTAINS("Bengaluru", [Site Impact]@row), CONTAINS("Chennai", [Site Impact]@row)), " -Southeast Asia", "")))

Let me know if this works for you!

Cheers,

Genevieve