A lambda that returns TRUE if the entry in a range is a duplicate of an earlier entry, otherwise, returns FALSE.  May be used with multiple rows or columns, by concatenating the row or column values to test for duplicate keys over multiple rows or columns.

Development Rationale

The purpose in developing this lambda is to simplify the detection of duplicate values.  Note that the first entry is not considered a duplicate, only subsequent matching entries are considered duplicates.

About the Lambda Calculation

This lambda encapsulates a call to the associated lambda λValueBetween from this library that simplifies determining whether a value falls within, above or below a range of values.  Based on its results, the constrained value is returned.

1  =LAMBDA(check_array,
2  LET(rowCount,ROWS(check_array),
3  colCount,COLUMNS(check_array),
4  dimensions,(rowCount>1)+(colCount>1),
5  seqs,SEQUENCE(rowCount,colCount),
6  IF(dimensions=2,#VALUE!,XMATCH(check_array,check_array)<>seqs)))

In line 1 above, the lambda is defined, with a single arguments capitalised as is usual for Excel functions called  check_array.

In line 2, LET function which handles the steps of calculation is commenced.  The value of the local variable rowCount is set to the count of rows in check_array.

In line 3, the local variable colCount is set to the count of columns in check_array.

in line 4, the local variable dimensions is set to the number of dimensions for check_array.  The result will be 1 (if just a single column or single row array is passed) and 2 if both dimensions are greater than 1.

In line 5, a local variable called seqs is set to a sequence of values matching the dimensions of check_array.

In line 6, the result of the LET function which becomes the result of the LAMBDA function is derived.  If check_array has two dimensions, the value #VALUE! is returned.  If a single dimension, the result of calling XMATCH to locate the first entry in check_array that matches the current entry is compared to the corresponding value in seqs.  If they are not equal, this indicates an earlier entry matches, thus the current entry is considered a duplicate.  An array of TRUE and FALSE values is returned.

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 λIsDuplicate

=λIsDuplicate(check_array)

where:
   check_array is a reference to a range, a nested expression that returns an array of values,or an array of constants that are evaluated to see if any of the entries is a duplicate of an earlier entry.  If the key values to be checked are in separate rows or columns, such as division, cost centre, account, they can be tested by concatenating them.  An example is shown in the referenced sample workbook below.

Sample File

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

Version History

¹ 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.