Org mode table range excludes empty cells

Question

For me this problem occurs when using org-lookup-first. However, I think this might happen in a lot of other circumstances as well. When specifying a range of cells for example via @1$1..@>$1 it looks like empty cells in this range are compressed/omitted (@1$1..@>$1 should select the whole column of a table). When selecting multiple columns like this this can cause weird behavior, because the two lists could have different lengths. The problem occurred to me when following this tutorial so maybe this helps.

Example:

#+TBLNAME: the-tbl
| First Line                |  1 |
| Second Line               |  2 |
| Third Line                |  3 |
| *some additional lines*   |    |
| Fifth Line                |  5 |
|                           |  6 |
| Seventh Line              |  7 |
| *another additional line* |    |
| Ninth Line                |  9 |
| Tenth Line                | 10 |
| Eleventh Line             | 11 |

| First Line   |   1 |   1 | OK                                                       |
| Third Line   |   3 |   3 | OK                                                       |
| No Line      | nil | nil | OK                                                       |
| Fifth Line   |   6 |   5 | Wrong because Line 4 has no value thus it is one shorter |
| Seventh Line |   7 |   7 | Only OK, because now both columns have a missing value   |
| Tenth Line   |  11 |  10 | Wrong again, because column 2 had another missing value  |
#+TBLFM: $2='(org-lookup-first $1 '(remote(the-tbl, @1$1..@>$1)) '(remote(the-tbl, @1$2..@>$2)))

Are there any ways to select columns like this, but maintaining empty cells? Or is there a better function than org-lookup-first for what I'm doing?

Answer 1

You need to do the calculations in E mode. The last two characters in the TBLFM line is ;E

If you ~C-c C-c~ on the first table, you get the second table.

#+TBLNAME: the-tbl
| First Line                |  1 |
| Second Line               |  2 |
| Third Line                |  3 |
| *some additional lines*   |    |
| Fifth Line                |  5 |
|                           |  6 |
| Seventh Line              |  7 |
| *another additional line* |    |
| Ninth Line                |  9 |
| Tenth Line                | 10 |
| Eleventh Line             | 11 |
#+TBLFM: $3='(org-lookup-first $1 '(remote(the-tbl, @1$1..@>$1)) '(remote(the-tbl, @1$2..@>$2)));E

#+TBLNAME: the-tbl
| First Line                |  1 |  1 |
| Second Line               |  2 |  2 |
| Third Line                |  3 |  3 |
| *some additional lines*   |    |    |
| Fifth Line                |  5 |  5 |
|                           |  6 |  6 |
| Seventh Line              |  7 |  7 |
| *another additional line* |    |    |
| Ninth Line                |  9 |  9 |
| Tenth Line                | 10 | 10 |
| Eleventh Line             | 11 | 11 |
#+TBLFM: $3='(org-lookup-first $1 '(remote(the-tbl, @1$1..@>$1)) '(remote(the-tbl, @1$2..@>$2)));E

---

See

(info "(org) Formula syntax for Calc")

or

Formula syntax for Calc

E: If and how to consider empty fields.

Without E empty fields in range references are suppressed so that the Calc vector or Lisp list contains only the non-empty fields. With E the empty fields are kept. For empty fields in ranges or empty field references the value nan (not a number) is used in Calc formulas and the empty string is used for Lisp formulas. Add N to use 0 instead for both formula types. For the value of a field the mode N has higher precedence than E.