Shorter SharePoint Calculated Column OR Statements

Tested in SharePoint 2010, 2013, 2016 and Online.

Available on Amazon!

Array Constants

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

The Excel version:

=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:

   

But…

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!