IfError() and Match()

[MikeWiese]

The IfError and Match functions are not implemented. Any chance of getting these implemented?

 

Also Vlookup, but this has already been mentioned on another thread

[Tanya]

Hello Mike,

The tasks scheduled for each release depend on their priority in our backlog, which is calculated depending on many factors, the most important of which is the client demand.

The three functions are currently not scheduled and I am unable to give you a time frame when they will be available. I can shed some light on their current priority. VLOOKUP and IFERROR are among the functions with the highest priority, while MATCH is with lower priority - I just created a feedback item for it. I can see that you have already found several items but here are the links to the feature requests for the discussed functions:

  - IFERROR function
  - MATCH function
  - VLOOKUP function

Although the functions are not built-in, you can implement them as custom functions and use them in your application. The public feedback items contain links to the resources you can use as a reference for the implementation.

Hope this is helpful.

Regards,
Tanya
Telerik by Progress

[Guy]

Hi

I am using the below class and it appears to work alright. Maybe someone at Telerik can correct any errors.

 

public class TelerikSpreadsheetIfError : FunctionWithArguments
    {
        public static readonly string FunctionName = "IFERROR";
        private static readonly FunctionInfo Info;

        public override string Name
        {
            get
            {
                return FunctionName;
            }
        }

        public override FunctionInfo FunctionInfo
        {
            get
            {
                return Info;
            }
        }

        static TelerikSpreadsheetIfError()
        {
            string description = "Excel compatible function. Returns value_if_error if the expression is an error and the value of the expression itself otherwise.";

            IEnumerable<ArgumentInfo> requiredArguments = new ArgumentInfo[]
            {
                new ArgumentInfo("value", "Any value or expression or reference", ArgumentType.Any),
                new ArgumentInfo("value_if_error", "The value to use if the first argument is an error.", ArgumentType.Any),
            };

            Info = new FunctionInfo(FunctionName, FunctionCategory.Logical, description, requiredArguments);
        }

        protected override RadExpression EvaluateOverride(FunctionEvaluationContext<object> context)
        {
            try
            {
                object firstArg = context.Arguments[0];
                object secondArg = context.Arguments[1];

                if ( firstArg is ErrorExpression )
                {
                    return secondArg as RadExpression;
                }
                else
                {
                    return firstArg as RadExpression;
                }
            }
            catch (System.Exception ex)
            {
                return ErrorExpressions.NullError;
            }
        }
    }

[Guy]

Ok, that evaluation function is not going to work in all cases. Try this instead:

 

protected override RadExpression EvaluateOverride(FunctionEvaluationContext<object> context)
        {
            try
            {
                object firstArg = context.Arguments[0];
                object secondArg = context.Arguments[1];

                var firstArgConst = ((RadExpression)context.Arguments[0]).GetValueAsNonArrayConstantExpression(false);

                if (firstArgConst is ErrorExpression )
                {
                    return secondArg as RadExpression;
                }
                else
                {
                    return firstArg as RadExpression;
                }
            }
            catch (System.Exception ex)
            {
                return ErrorExpressions.NullError;
            }
        }

[Anna]

Hi Guy,

I tested your code with all possible errors and scenarios that I could think of and that I found in the xlsx specification. Looks good! Thank you for providing it in the forum where others might make use of it as well.

Regards,
Anna
Progress Telerik