SharePoint: IF Function

“IF function” will return something if the first value is “true” and something else if the second is false.

The “If function” is the most straightforward function ever. It’s quite handy, but it has some tricks that you should know.

Usage

It follows a simple pattern.

1. Comparison
2. Value is true, return something
3. Return something else

Example:

==IF([VAT]="Standard",23,13)
The result will be
23

It’s a quick way to return a value or another based on a condition. You can do all sorts of comparisons as long as a result is true or false. For example:

You’re using another function to get results and then compare them:

=IF(SUM([Col1],[Col2],[Col3]) > 100,"Too much","OK")

Here we’re combining two comparisons with a function, and if both are true, then we return a result.

=IF(AND(VAT="Standard",location="Lisbon"),23,13)

As you can see, you can combine a lot, with a simple function. Try although to keep formulas simple. Otherwise, you’ll have a lousy time debuting them.

Limitations

You can nest up to 7 If statements. I have nesting (see below in the recommendations), but this is the only way to achieve if you have multiple values. Be aware of this limitation and, if you have more than 7, you can:

1. Break it down into smaller expressions and use different hidden calculated columns to make the comparisons (yes it’s ugly)
2. Have a service that parses the information for you. For example, have an empty column that will contain the result and, when the VAT column is changed, Power Automate can update the value automatically.

Recommendations:

1. Always define the “The data type returned from this formula” in SharePoint. If something comes out of the “If” that you don’t expect, you’ll see an #Error and flag that either the formula or the error is incorrect.
2. I don’t like nest If statements, but since there’s no switch function, you have to use it if you have multiple choices. Try although to keep it at a minimum since the formula will become quite unreadable fast:

==IF([VAT]="Standard",23,IF([VAT]="Reduced",13,IF([VAT]="Food",6,0)))
The result will be:
23

Sources:

Microsoft’s IF Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by Robert Anasch on Unsplash