Looking up values in an org spreadsheet

Question

I imagine it would be possible, with the tables

| food    | kcal/hg |
|---------+---------|
| sausage |     281 |

and

    | food    |   g | kcal |
    |---------+-----+------|
    | sausage | 100 |      |

that is, with a table of food names and kcal/hg values and a table of names and g values, automatically to calculate the kcal values in the second list. But how exactly is that to be done?

Answer 1

You could use the lisp function memq for the lookup as demonstrated in the Org example below. I use the Literal mode switch L. This imposes the restriction that there are only one-word identifier in the food column.

Furthermore I use quote to avoid evaluation of the symbols in the table formula.

Please, use the table debugger to see what is going on (Tbl menu item Debug Formulas).

#+NAME: kcal_per_hg
| food      | kcal/hg |
|-----------+---------|
| sausage   |     281 |
| something |     100 |
| other     |     200 |

and

| food    |   g |  kcal |
|---------+-----+-------|
| sausage | 100 | 281.0 |
| other   | 123 | 246.0 |
#+TBLFM: $3='(* 0.01 $2 (cadr (memq (quote $1) (quote (remote(kcal_per_hg,@I$1..@II$2))))));L

Note that this solution is tailored to your special problem since the words are actually looked-up in the full table kcal_per_hg.

There follows a more general but also more complicated variant. It only searches the first column of the two-column table kcal_per_hg for food.

#+NAME: kcal_per_g
| food      | kcal/g |
|-----------+--------|
| something |    100 |
| sausage   |    281 |
| other     |    200 |

and

| food    |   g |  kcal |
|---------+-----+-------|
| other   | 123 | 246.0 |
| sausage | 100 | 281.0 |
#+TBLFM: $3='(* $2 0.01 (cl-loop for p on (quote (remote(kcal_per_g,@I$1..@II$2))) by #'cddr if (eq (quote $1) (car p)) return (cadr p)));L

If each row of the table to be searched has N columns instead of two you can replace cddr with (apply-partially #'nthcdr N).