extract unique values matching a text string

nabilmourad 

EVALUATE

This is a 'left over' from the old Macro language that preceded VBA.  In modern Excel it is only recognised if it is used within a Named Formula.  It is used to evaluate a formula or expression that is in the form of text and return the result. 

Syntax

= EVALUATE(formula_text)

Formula_text is the expression in the form of text that you want to evaluate.

Remarks:

Using EVALUATE is similar to selecting an expression within a formula in the formula bar and pressing the Recalculate key (F9 in Microsoft Excel for Windows). EVALUATE replaces an expression with a value.

Note: A named formula is just a snippet of a formula held as a text string.  It is only processed when a formula placed within a cell refers to it, in which case the snippet is calculated as if it were an inner element of the cell formula, nested within it.  I used named formulas to give meaning to the values calculated by the inner elements of a nested formula and to make the cell formula shorter and more easily understood.  Other plusses are that a named formula is always evaluated as an array formula and, in this case, it is Excel's most comprehensive calculation environment.