A lambda to simplify the process of determining if a range or array is sorted in ascending order which may be necessary to check or monitor if, for example, XLOOKUP and XMATCH functions have been used based on binary searches in ascending order. It may be necessary to have a check formula that warns if the user changes the sort order in a table or data range.

Development Rationale

The purpose in developing this lambda is to simplify the steps required in a checking formula, such as in a master checklist, to determine if data is sorted in an order that may be required, such as when binary searches are implemented by XLOOKUP, XMATCH functions.  The lambda simply returns TRUE or FALSE to indicate if this condition has been met.

About the Lambda Calculations

Initially, we developed an IsSorted lambda that could take optional arguments that paralleled the SORT function - as to which column or row should be checked, the order of sorting required, and whether the sort was top-to-bottom or left-to-right.  But upon reflection, that required many more calculation steps to handle possible errors and at the end of the day, it is possible to apply the test not against a whole array, nominating the key column or row, but more simply against a specific row or column that must be in ascending order.  So we attacked the problem again to make a more straight-forward solution.

In this version of the lambda library, the function has been simplified again, reducing unnecessary checking to streamline the formula significantly.

So this lambda was simplified to take two arguments only, the column or row to be checked for whether or not it is sorted, and an optional argument as to whether it is in descending order, which will default to FALSE, meaning the base logic is to check for ascending sorted data.

1  =LAMBDA(target_array,[desc_order],
2    LET(leftRight,ROWS(target_array)=1,
3    twoDim,AND(ROWS(target_array)>1,COLUMNS(target_array)>1),
4    IF(twoDim,#REF!,AND(target_array=SORT(target_array,1,IF(desc_order,-1,1),leftRight)))))

In line 1 above, the lambda is defined, with two arguments, the second of which is optional: desc_order.  Just as in the notation in Excel, the brackets indicate an argument is optional and may be omitted when the functions is called.

In line 2, the LET function in the third argument of the LAMBDA encases the step-by-step calculation of the required result.  Initially, a local value called leftRight is derived, determining whether the required checking sort will need to be left-to-right, or top-to-bottom.  If the row count of target_array is 1 - indicating only one row of values - the sort needs to be left-to-right, and the value assigned will be TRUE, otherwise, it will be FALSE.

In line 3, the local value twoDim is derived, determining whether the target_array is two-dimensional or not.  The check formula is designed to only work with a single column or a single row!   It uses and AND function to determine if both the row count (ROWS function) and column count (COLUMNS function) for targetArray are both greater than 1.  If it is a two dimensional array, the evental result will be #REF!.

Finally, in line 4, the return value of the LET function, which is the result returned by the LAMBDA.  It is the result of calling an IF function that sets the return value to #REF! if the value of twoDim is TRUE.  Otherwise the return value is the result of calling an AND function and passing it two arrays that are compared to see if all values are equal.  The first array is the target_array and the second is the result of calling a SORT on target_array, setting the sort order based on use_order and the sort by columns argument based on leftRight.  If after sorting, each value in the original array agrees to the sorted values, then the AND function returns TRUE, otherwise it will return FALSE.

Defined Name Settings

To create the lambda, simply use the define name¹ feature in Excel to define this named lambda in the workbook where it will be used.  The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.

Syntax of λIsSorted

=λIsSorted(target_array,[desc_order])

where:
   targetArray is an array or reference to an array of values that is one column wide or one row deep.  This is the column or row which is the key value that must be in ascending or descending order for the formulas accessing that range to be producing valid results.  If target_array is two dimensional, the lambda returns #REF!.

   desc_order is an optional argument that is expected to be a logical value TRUE or FALSE.  If omitted, FALSE is the default value.  If a number is passed, 0 is coerced to FALSE, all other values coerce to TRUE.  Text strings will cause the #VALUE! error to be returned.

Sample File

An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.

 Version History

¹  The required settings are shown below.  Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

² You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.