Excel formula with dynamic inputs & dynamic outputs

Question

I have two table ranges in excel. One will have the conditions and equivalent formulas to be used for those conditions. And in other table, I have conditions and their input values that should be given to the formulas. I need to substitute the inputs with the relevant condition formula and get the output. How to do it directly in MS excel without using VBA

I've tried using What if analysis and Lambda formulas. But in anymeans I couldn't get that.

Answer 1

There are many options, here I choose to include an old Excel v4.0 macro function called EVALUATE(). I made a custom function through name manager called EVAL() which refers to:

=LAMBDA(x,EVALUATE(x))

Then I simply went over your data using MAP(), gave all values a variable and used SUBSTITUTE() to make a mathematical sound string to be evaluated:

Formula in G2:

=MAP(D2:D7,E2:E7,F2:F7,LAMBDA(x,y,z,EVAL(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP(x,A2:B4,2,0),E$1,"*"&y),F$1,"*"&z),"+*","+"))))