Shorter SharePoint Calculated Column OR Statements

Tested in SharePoint 2010, 2013, 2016 and Online.

This is a sample from my soon to be published book “SharePoint Calculated Columns and Validation Formulas”. If you would like to see a lot more about what you can do with Calculated Columns, and maybe win a free book, attend my session at SharePoint Cincy 2018 on April 10th!

Array Constants

Excel will often let you use an array of values where you would normally use a range of cells.

=VLOOKUP( [StatusCode], {"a","Active";"i","Inactive";
"c","Closed"}, 2, 0 )

The array constant part of the formula is from “{“ to “}” and represents a two dimensional table. The Excel range equivalent to this array looks like this:



While that is kind of cool, it does not always work in SharePoint Calculated Columns. One place it does work is with an OR function where it works much like the “IN” keyword in other technologies. The following two functions are equivalent:

=IF( OR( StateCode="OH", StateCode="KY", StateCode="IN", StateCode="IL" ),
"Central Region", "Other" ) =IF( OR( StateCode = {"OH","KY","IN","IL"} ), "Central Region", "Other")

(If you see this anywhere other than at TechTrainingNotes.blogspot.com, it was stolen and used without permission!)

Both of the above produce this result:


Pretty cool!

No comments:

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.