Re: [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table)

[Bruno Barbier]

Uwe Brauer <oub@mat.ucm.es> writes:

>>>> "UB" == Uwe Brauer <oub@mat.ucm.es> writes:
>
>>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
>>> Hi Uwe,
>
>>> Uwe Brauer <oub@mat.ucm.es> writes:
>>>> so the question is what is equivalent of VLOOKUP in org.
>
>>> Did you check these lookup functions in the Org manual?
>
>>> (info "(org) Lookup functions")
>
>
> I ask differently why does the following solution not work
>
> #+Name: table1
> | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith  |   2 |   3 |   4 |   6 |   7 |     22 |
> | Miller |   2 |  10 |   1 |   1 |   5 |     19 |
> | Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
> #+TBLFM: $7=vsum($2..$6);f2
>
>
>
> #+Name: final
> | Name   | Some | ResSh1       |
> |--------+------+--------------|
> | Smith  |    4 | [22, 19, 18] |
> | Miller |    4 | [22, 19, 18] |
> | Wick   |    4 | [22, 19, 18] |
> |--------+------+--------------|
> #+TBLFM: @2$3..@>$3=remote(table1,@2$7..@>$7)

The ref "remote(table1,@2$7..@>$7)" targets a range, containing all fields of
colum 7.  So, for each field, you get the same value: an array
containing all the field values: [22, 19, 19].


>
> But this solution does, what does @@#$7 mean?

See (info "(org) References"), in the section:
   "Field coordinates in formulas"

"‘@#’ is substituted with the row number of the field where the
current result will go to".


> #+Name: final2
> | Name   | Some | ResSh1 |
> |--------+------+--------|
> | Smith  |    4 |     22 |
> | Miller |    4 |     19 |
> | Wick   |    4 |     18 |
> |--------+------+--------|
> #+TBLFM: @2$3..@>$3=remote(table1,@@#$7)

With this formula, your remote reference targets the field, that is in
the same row number as the computed field in final.

Here is the same table, with some extra columns that show the
intermediate steps:

   #+Name: final2
   | Name   | Some | ResSh1 | What is '@#'? | Which ref? |
   |--------+------+--------+---------------+------------|
   | Smith  |    4 |     22 |             2 | @2$7       |
   | Miller |    4 |     19 |             3 | @3$7       |
   | Wick   |    4 |     18 |             4 | @4$7       |
   |--------+------+--------+---------------+------------|
   #+TBLFM: @2$3..@>$3=remote(table1,@@#$7)
   #+TBLFM: @2$4..@>$4=@#
   #+TBLFM: @2$5..@>$5='(concat "@" (format "%d" @#) "$" "7")

Bruno



>
>
>
> -- 
> Warning: Content may be disturbing to some audiences
> I strongly condemn Putin's war of aggression against Ukraine.
> I support to deliver weapons to Ukraine's military. 
> I support the NATO membership of Ukraine.
> I support the EU membership of Ukraine. 
> https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/