CALC: How to lookup a formula instead of a value

[iperetta]

Hi guys

I’m aware of this: Reference a formula instead of a value?

But I need to lookup the formula in a table like this:

=VLOOKUP(A5,Sheet1.$A$3:$C$183,2,1)

Is there a way of doing something as = FORMULA(VLOOKUP(A5,Sheet1.$A$3:$C$183,2,1)) ?

EDITED:

This piece of information is due to the answer of Lupp (thanks anyway!). In my university, there is a process to you earn points as a professor, but the activities have several different ways of being calculated.

Examples (all fake ones):

• Give class — 0.5 point per hour per student (Formula = 0.5 * hour * student)
• Write a paper — 150 points (Formula = 150)
• Being a coordinator — 30 points per month (Formula = 30 * month)

And I need to select which ones I did in the last period, as well as calculate how many points do I have. I can select either none, one or more times each item (e.g., I could give 3 classes and be coordinator of two different fields).

[Lupp]

You can only use VLOOKUP if the formulas are text content or text results in the first column of the range given as lookup table.

The much more flexible combinations of MATCH with either INDEX or OFFSET you can also use to match against formulas and to get access to a corresponding value with the help of the result. As you need an array of formulas to search for a match of a specific formula given as text, you need to apply MATCH under array evaluation. See this attached example.

However, this is a very strange attempt, and I would really ask you to give a realistic example of a task for whose solution such an approach is useful.

[iperetta]

Thanks, @Lupp, I could figure it out with your help… But now I have another issue with how to apply those formulas in the new spreadsheet…

[iperetta]

So, I did a spreadsheet containing all those rules and related formulas. Actually, after the help of Lupp I was able to make this:

=MID(FORMULA(INDIRECT("Sheet1.G"&MATCH(A5,Sheet1.$A$1:$A$183,1))),2,999)

and I’m able to do these in another spreadsheet:

• Give class [course 1] (0.5 * hour * student)
• Give class [course 2] (0.5 * hour * student)
• Give class [course 3] (0.5 * hour * student)
• Being a coordinator [lab 1] (30 * month)
• Being a coordinator [trainees] (30 * month)

Now I’m stuck in how to use those “text formulas” to apply to the new spreadsheet…

EDITED:

Here is a “minimal” example: example.ods

[Lupp]

I would try to help you further if I understood your point. Please make accessible a relevant example (depersonalized // enough specimen to fight misunderstandings likely if given too few examples) of what you have and the results you try to achieve.

Anyway it still seems extremely strange to me to lookup for formulae. I still don’t know what A5 (e.g.), Sheet1.$A$1…, and Sheet1.G…somerange actually contain. In what Sheet.Cell(s) does your new formula work?

[iperetta]

Hi @Lupp. I’ve just included an .ODS with a minimal example. If you could help me, I would appreciate very much. If you could post the full solution, I’ll give you the answer.

[Lupp]

Ok. I am interested due to an “academic” interest: Paradigmatic Q.
You will know yourself best that you are fighting “The slings and arrows of outrageous fortune bureaucracy…”.
As long as there isn’t any restriction to the expressions for the contributions to your points-account this will never finally succeed. As soon as bureaucracy invents a new variant (using two thresholds, e.g. instead of one at maximum) you will have to not just enter new criteria values, but new formulae, too.

[Lupp]

Since Calc does not provide an EVALUATEFORMULA function this cannot be done by adding the formula itself as a text into a parametrising cell. The way you try to cope with the problem is nonetheless problematic, imo. (I will be back later if I find some more spare time.)