Create a Custom Function with Excel

The LAMBDA function allows you to create new functions (without using macros).

Once the LAMBDA function is defined, simply save it in the name manager to create a custom function.


Example of a Custom Function

The goal here is to create a new function IF_DUPLICATE that performs the following test:

=IF(COUNTIF(A$2:A$13,A2)>1,"Duplicate","-")

This formula displays Duplicate when the value is present more than once in the range of values and - when it's not the case (using the COUNTIF and IF functions):

excel countif duplicates custom function

The equivalent version using LAMBDA is:

=LAMBDA(range,value,IF(COUNTIF(range,value)>1,"Duplicate","-"))(A$2:A$13,A2)

And produces the same result:

excel countif duplicates lambda custom function
If the operation of LAMBDA is not clear to you, refer to the LAMBDA function page.

To create a custom function, copy the LAMBDA function from this last formula and open the Name Manager:

excel name manager png custom function

Click on New, give a name to the function, and paste the LAMBDA function (only) into the last field:

excel custom function manager

The new function created will then be immediately available:

excel custom function

You just need to enter the range of values and the value to test here:

excel custom function if

To get:

=IF_DUPLICATE(A$2:A$13,A2)
excel custom function if duplicate
If needed, you can download the Excel file used here: custom-function.xlsx
Note: requires Office 365.