SharePoint: ISNA Function

Following the “IS” functions theme, we have the ISNA function. This checks for the “#N/A,” an error that we know all too well in Excel. These functions return TRUE or FALSE, depending on the value that they are checking.

Usage

It follows a simple pattern.

1. Value

Example:

Col1 has the value "#N/A"

ISNA([Col1])

will return:

TRUE

You don’t necessarily need to check for errors, but you absolutely should, especially for columns that return values to the user. Nothing worse than the user seeing “#N/A,” something that can mean absolutely nothing to them.

Limitations

This function will only check for this type of error. If you have a “#REF” error, where a reference is incorrect, this function’s return will be FALSE. This makes things a bit awkward since you have to use multiple functions to check for a specific error.

Recommendations:

1. Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose to the user the “message” columns that have useful error messages or actions that they can take to solve the problem.
2. Always deal with errors. Leaving them for the user to see is horrible, but also you can bring trouble to your own code. If you don’t validate a column, it can be used after other calculations, and the error will cascade. You can have an error in one column, and the issue could be in whole different columns, so deal with issues where they happen.
3. Don’t nest error checking. There’s no real reason to do it, but if you find yourself in a situation where you have nested ISNA functions in a formula, you should check it carefully because you’re probably doing something wrong.
4. Please note that formulas may have localization-based differences. For example, you should write “ISNA” with a comma separating each of the arguments, but if your regional settings are set to Portugal, you should use “;” instead.

Sources:

Microsoft’s ISNA Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by Sigmund on Unsplash