# Looking up values in an org spreadsheet

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?

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)`.