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](https://www.excel-pratique.com/view/en/tips/img/custom-function/excel-countif-duplicates.png)
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](https://www.excel-pratique.com/view/en/tips/img/custom-function/excel-countif-duplicates-lambda.png)
To create a custom function, copy the LAMBDA function from this last formula and open the Name Manager:
Click on New, give a name to the function, and paste the LAMBDA function (only) into the last field:
![excel custom function manager](https://www.excel-pratique.com/view/en/tips/img/custom-function/excel-custom-function-manager.png)
The new function created will then be immediately available:
![excel custom function](https://www.excel-pratique.com/view/en/tips/img/custom-function/excel-custom-function.png)
You just need to enter the range of values and the value to test here:
![excel custom function if](https://www.excel-pratique.com/view/en/tips/img/custom-function/excel-custom-function-if.png)
To get:
=IF_DUPLICATE(A$2:A$13,A2)
![excel custom function if duplicate](https://www.excel-pratique.com/view/en/tips/img/custom-function/excel-custom-function-if-duplicate.png)