# EXCEL: IF value for multiple cells

I have a 3 columns, some of which have countries in them like so

``````I                    J                    K
--------------       -----------------    ----------------
Denmark
Finland
London               United Kingdom
123 street           London               United Kingdom
``````

The problem is that i want to add the countries into a single cell instead of having them in multiple cells

``````L
------------
Denmark
Finland
United Kingdom
United Kingdom
``````

I Have tried

``````=IF(OR(AND(J1=" United Kingdom","United Kingdom",""),AND(K1=" United Kingdom","United Kingdom",""),AND(L1=" United Kingdom","United Kingdom",""),AND(I1=" United Kingdom","United Kingdom","")),"Correct","Wrong")
``````

and

``````=IF(OR(J1=" United Kingdom","United Kingdom",""),OR(K1=" United Kingdom","United Kingdom",""),OR(L1=" United Kingdom","United Kingdom",""),OR(I1=" United Kingdom","United Kingdom","")),"Correct","Wrong")
``````

but this gives me an error

Does anybody know why this is not working or a better way to do this?

-

## migrated from stackoverflow.comOct 6 '12 at 23:20

This question came from our site for professional and enthusiast programmers.

Create a list of your seven countries and create a dynamic named range of this list. Let's say you named it `CountryList`. You could keep this list on a hidden worksheet if need be. Using a dynamic named range just means you can add a new country to the list without having to adjust any of the formulae

Now in your final column put the formula

use the formula with all the `OR()` and `TRIM()` that you posted but instead of `TRIM(J1)="United Kingdom"` rather use `ISERROR(MATCH(J1, CountryList, 0 ))` to test if the cell matches something in your range and instead of `"Correct"` use `INDEX(CountryList, MATCH(J1, CountryList))` to extract the name from the list

So putting it all together:

``````=IF(OR(OR(ISERROR(MATCH(J1, CountryList, 0 ))),OR(ISERROR(MATCH(K1, CountryList, 0 ))),OR(ISERROR(MATCH(L1, CountryList, 0 ))),OR(ISERROR(MATCH(I1, CountryList, 0 )))),INDEX(CountryList, MATCH(J1, CountryList)),"Wrong")
``````
-

I have actually solved the problem using the following formula

``````=IF(OR(OR(TRIM(J1)="United Kingdom"),OR(TRIM(K1)="United Kingdom"),OR(TRIM(L1)="United Kingdom"),OR(TRIM(I1)="United Kingdom")),"Correct","Wrong")
``````
-
This solution only works for one country, you have many in your example. The solution provided by @mtariq is much more effective. Have you tried using trim() as he suggested in his comments? – Dan Oct 5 '12 at 11:31
also trim(" United Kingdom") returns "United Kingdom", it is more robust than hard coding the leading space. What if a bug causes two leading spaces, or a trailing space? – Dan Oct 5 '12 at 11:33
yes i have tried @mtariq but this way does not work, i know this way only works for 1 country but i only have 7 countries so i done this in 7 different columns and concatenated the columns then after and it worked find – Niall Oct 5 '12 at 12:01
Then rather create a list of your seven countries and make your function check in that list. I have no doubt that you can make @mtariq's method work though unless your issue is that the country is not always the last field (which is not demonstrated in your example) – Dan Oct 5 '12 at 12:07

Try this (in cell `L1`)

``````=IF(\$K1<>"", \$K1, IF(\$J1<>"", \$J1, IF(\$I1<>"", \$I1, "Not Provided")))
``````

Have not tested this but should work.

Edit

based on the example you gave i assumed that column K would either be blank or a country. If it can contain spaces then try

`=IF(TRIM(\$K1)<>"", \$K1, IF(TRIM(\$J1)<>"", \$J1, IF(TRIM(\$I1)<>"", \$I1, "Not Provided")))`

based on the example you gave i assumed that column `K` would either be blank or a country. If it can contain spaces then try `=IF(TRIM(\$K1)<>"", \$K1, IF(TRIM(\$J1)<>"", \$J1, IF(TRIM(\$I1)<>"", \$I1, "Not Provided")))` instead. – mtariq Oct 5 '12 at 11:25