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):
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:
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:
The new function created will then be immediately available:
You just need to enter the range of values and the value to test here:
To get:
=IF_DUPLICATE(A$2:A$13,A2)