0

I am having some difficulty creating a formula in my Sharepoint List. I have a column that is a list of countries and I am trying to create a formula that will check the value of the column and spit out a specific response based on the value. So if the country is Afghanistan, I want it to report in the column "CENTCOM". If it is Kenya, then "AFRICOM", etc. I have the list of the all the countries and I can jam them all into the formula, I'm just not sure of the syntax. Is there some way to do it like "=IF(Country="Afghanistan","CENTCOM" but instead of just listing one country, actually put a list of countries in there? And then if the country is not on that list, move to the next list?

I'm sorry if this is confusing, I'm new to this and trying to figure this out with no prior programming experience. Thanks for your help!

New contributor
Brian is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

The way you achieve this is through nested IF statements, where you put the next IF in the place of the "false" value, and at the end of the final IF you have to put a real "false" value that will be returned if no matches were found (this can be an empty string). So, using the two things you gave as an example you would do

=IF(Country="Afghanistan", "CENTCOM", IF(Country="Kenya", "AFRICOM", IF(Country="Iceland", "ICECOM", "Unknown"))) 

The IF function documentation. says that the limit is 7 nested IFs, but this other answer claims that for 2013 and later, you can have 19 nested IFs, and has a workaround for how to chain even more.

In order to check against a list of country names that would all return the same value, you would have to use ORs, so something like

=IF(OR(Country="Afghanistan", Country="Pakistan", Country="Otherstan"), "CENTCOM",
 IF(OR(Country="Kenya", Country="Uganda", Country="Rwanda"), "AFRICOM",
 IF(OR(Country="Iceland", Country="Greenland), "ICECOM", "")))

The OR function documentation claims that you can have up to 30 conditions to check in one OR operation, however, if you believe that other answer's claim that in 2013 you can have up to 19 IFs, then the documentation may be wrong...

Another handy reference - the alphabetical list of all functions you can use.

Your Answer

Brian is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.