Hello, I'd appreciate any thoughts on my question. Was hoping that I can accomplish this without using macros, but open using macros if it's the only way to solve the problem. Here's the scenario: on tab A, I have 3 variables - a, b, and c. The variables will have a unique amount assigned and may be repeated with different amounts. More on column C below.
On tab B, I've assigned unique math formulas to each of the variables. For example, a=x*2; b=x+100; c=x+50. "x" is meant for the amounts from tab A. In my real world problem, I am going to have 50+ variables each with their own unique math formula. All the math formulas are relatively simple.
Is it possible to use a lookup function (e.g., xlookup) to pull the formulas on tab B to compute "results" (Column C) on tab A? Specifically, the formula would first lookup the formulas on tab B based on the variables on tab A. Then, the solution would run the amounts on tab A through the formula to output the result in Column C. Based on my example, the results on tab A, Column C would be:
Solutions that I've considered are: using the "LET" function. Since LET is a relatively new Excel function, I don't know if it is capable of pulling the formula from tab B and then put it in tab A and then calculate it based on a variable 'x'. I have also used very long if/then statements in the past for each variable, but as you can imagine, the formulas are super long and very inefficient.
| Column A | Column B | Column C |
| a | $100 | <result> |
| b | $500 | <result> |
| c | $2,000 | <result> |
| d | $400 | <result> |
On tab B, I've assigned unique math formulas to each of the variables. For example, a=x*2; b=x+100; c=x+50. "x" is meant for the amounts from tab A. In my real world problem, I am going to have 50+ variables each with their own unique math formula. All the math formulas are relatively simple.
Is it possible to use a lookup function (e.g., xlookup) to pull the formulas on tab B to compute "results" (Column C) on tab A? Specifically, the formula would first lookup the formulas on tab B based on the variables on tab A. Then, the solution would run the amounts on tab A through the formula to output the result in Column C. Based on my example, the results on tab A, Column C would be:
| Column A | Column B | Column C |
| a | $100 | $200 |
| b | $500 | $600 |
| c | $2,000 | $2,050 |
| a | $400 | $800 |
Solutions that I've considered are: using the "LET" function. Since LET is a relatively new Excel function, I don't know if it is capable of pulling the formula from tab B and then put it in tab A and then calculate it based on a variable 'x'. I have also used very long if/then statements in the past for each variable, but as you can imagine, the formulas are super long and very inefficient.
