equivalent of VLOOKUP (in ods) to org-table

equivalent of VLOOKUP (in ods) to org-table

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 3422 bytes --]

Hi

I have regularly to deal with large tables, that I split in smaller ones and then try to connect them.

I have to export the resulting big table to ods, and in the ods case, I have several sheets and use the 
VLOOKUP command, like this

#+begin_src 
#+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: table2   
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
| Miller |   2 |   1 |   6 |   9 |   3 |     21 |
| Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
#+TBLFM: $7=vsum($2..$6);f2

| Name   |   | ResSh1                                          | ResSh2                                          | Total       |
|--------+---+-------------------------------------------------+-------------------------------------------------+-------------|
| Smith  | 3 | /=VLOOKUP(final.A2,table1.$A$1:$G$3,7,FALSE())/ | /=VLOOKUP(final.A2,table2.$A$1:$G$3,7,FALSE())/ | =SUM(C2:D2) |
| Miller | 3 | /=VLOOKUP(final.A3,table1.$A$1:$G$3,7,FALSE())/ | /=VLOOKUP(final.A3,table2.$A$1:$G$3,5,FALSE())/ | =SUM(C3:D3) |
| Wick   | 4 | /=VLOOKUP(final.A4,table1.$A$1:$G$4,7,FALSE())/ | /=VLOOKUP(final.A4,table2.$A$1:$G$4,7,FALSE())/ | =SUM(C4:D4) |
#+TBLFM: $5=SUM(@4$3:@4$4)
#+end_src


Maybe there are other solution in ods I don't  know, so the question is what is equivalent of VLOOKUP in org.

I came up with the remote command that results in a similar result, (I did not want to use third party packages like 
orgtbl-aggregate. 

#+begin_src 
#+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: table2   
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
| Miller |   9 |   4 |   6 |   9 |   3 |     31 |
| Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
|--------+-----+-----+-----+-----+-----+--------|
#+TBLFM: $7=vsum($2..$6);f2

#+Name: final
| Name   | Some | ResSh1 | ResSh2 | Final |
|--------+------+--------+--------+-------|
| Smith  |    4 |     22 |     33 |    59 |
| Miller |    4 |     19 |     31 |    54 |
| Wick   |    4 |     18 |     18 |    40 |
|--------+------+--------+--------+-------|
#+TBLFM: $3=remote(table1,@@#$7)::$4=remote(table2,@@#$7)::$5=vsum($2..$4);f2
#+end_src


The syntax with @@#$7 look cryptic to me, but I found it somewhere and it works
if somebody can suggest a simpler command that is closer to VLOOKUP I would be grateful

Thanks and regards

Uwe Brauer 

-- 
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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: equivalent of VLOOKUP (in ods) to org-table

[Bruno Barbier]

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")


Bruno.



>
> I came up with the remote command that results in a similar result, (I did not want to use third party packages like 
> orgtbl-aggregate. 
>
> #+begin_src 
> #+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: table2   
> | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
> | Miller |   9 |   4 |   6 |   9 |   3 |     31 |
> | Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
> |--------+-----+-----+-----+-----+-----+--------|
> #+TBLFM: $7=vsum($2..$6);f2
>
> #+Name: final
> | Name   | Some | ResSh1 | ResSh2 | Final |
> |--------+------+--------+--------+-------|
> | Smith  |    4 |     22 |     33 |    59 |
> | Miller |    4 |     19 |     31 |    54 |
> | Wick   |    4 |     18 |     18 |    40 |
> |--------+------+--------+--------+-------|
> #+TBLFM: $3=remote(table1,@@#$7)::$4=remote(table2,@@#$7)::$5=vsum($2..$4);f2
> #+end_src
>
>
> The syntax with @@#$7 look cryptic to me, but I found it somewhere and it works
> if somebody can suggest a simpler command that is closer to VLOOKUP I would be grateful
>
> Thanks and regards
>
> Uwe Brauer 
>
> -- 
> 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/

Re: equivalent of VLOOKUP (in ods) to org-table

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 717 bytes --]

>>> "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")


Yes of course, but I am unable to obtain the same result as I do using
the remote call.

Uwe 
-- 
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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

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

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1589 bytes --]

>>> "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)

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

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




-- 
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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

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/

Re: [the cryptic @@#$7]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 920 bytes --]


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

   > 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].



   > 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".

Aha, and has the benefit that the lookup call is not needed, but is more cryptic thanks for clarifying this.


-- 
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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [the cryptic @@#$7]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 4428 bytes --]


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

> 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].



> 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".



> 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")


Here is an example where the org-lookup-first method seems to fail

#+begin_src 

#+Name: Table1A
| / | <>       |  <> |  <> |  <> |  <> |  <> |    <> |
|   | Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | E1Tot |
|---+----------+-----+-----+-----+-----+-----+-------|
|   | Smith    |   2 |   3 |   4 |   6 |   7 |    22 |
|   | Brown    |   7 |  10 |   3 |   4 |  12 |    36 |
|   | Graham   |   4 |   5 |   1 |  10 |   7 |    27 |
|   | Jones    |  14 |   9 |   1 |   8 |  13 |    45 |
|   | Thompson |  14 |   4 |  11 |   9 |  11 |    49 |
|   | Walker   |   3 |  11 |   5 |   3 |   6 |    28 |
#+TBLFM: $8=vsum($3..$7);f2

#+Name: Table2A
| / | <>       |  <> |  <> |  <> |  <> |  <> |    <> |
|   | Name     | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | E2Tot |
|---+----------+-----+-----+-----+-----+-----+-------|
|   | Smith    |   1 |   3 |   5 |   8 |   9 |    26 |
|   | Brown    |  14 |   9 |  10 |   3 |   8 |    44 |
|   | Graham   |   5 |  14 |   1 |   5 |   6 |    31 |
|   | Jones    |  11 |  11 |   3 |   1 |   9 |    35 |
|   | Thompson |   7 |  10 |   3 |   9 |   5 |    34 |
|   | Walker   |   2 |   3 |   3 |  10 |   2 |    20 |
#+TBLFM: $8=vsum($3..$7);f2


#+Name: Final1A
| / | <>       | <>    | <>    | <>    |
|   | Name     | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
|   | Smith    | nil   | nil   | 2 nil |
|   | Brown    | nil   | nil   | 2 nil |
|   | Graham   | nil   | nil   | 2 nil |
|   | Jones    | nil   | nil   | 2 nil |
|   | Thompson | nil   | nil   | 2 nil |
|   | Walker   | nil   | nil   | 2 nil |
#+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1)) '(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2 '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A, @I$7..@II$7)))::$5=vsum($3..$4);f2


#+Name: Final2A
| / | <>       |    <> |    <> |    <> |
|   | Name     | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
|   | Smith    |    22 |    26 |    48 |
|   | Brown    |    36 |    44 |    80 |
|   | Graham   |    27 |    31 |    58 |
|   | Jones    |    45 |    35 |    80 |
|   | Thompson |    49 |    34 |    83 |
|   | Walker   |    28 |    20 |    48 |
#+TBLFM: $5=vsum($3..$4);f2::@3$3..@>$3=remote(Table1A,@@#$8)::@3$4..@>$4=remote(Table2A,@@#$8)




#+Name: Final3
| / | <>       |    <> |    <> |    <> |
|   | Name     | E1Tot | E2Tot | Total |
|---+----------+-------+-------+-------|
|   | Smith    |    22 |    26 |    48 |
|   | Brown    |    36 |    44 |    80 |
|   | Graham   |    27 |    31 |    58 |
|   | Jones    |    45 |    35 |    80 |
|   | Thompson |    49 |    34 |    83 |
|   | Walker   |    28 |    20 |    48 |
#+TBLFM: $3=remote(Table1A,@@#$8)::$4=remote(Table2A,@@#$8)::$5=vsum($3..$4);f2
#+end_src






> Bruno






-- 
Warning: Content may be disturbing to some audiences
I strongly condemn Hamas terroristic attack on Israel.
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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: [the cryptic @@#$7]

[Bruno Barbier]

Hi Uwe,

Uwe Brauer <oub@mat.ucm.es> writes:
> Here is an example where the org-lookup-first method seems to fail

> #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1)) '(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2 '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A, @I$7..@II$7)))::$5=vsum($3..$4);f2

You forgot to update the column numbers: the names to look for are now in
the column '2', and, the totals are now in column '8'. This should work:


    #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$2..@II$2)) '(remote(Table1A, @I$8..@II$8)))
    #+TBLFM: $4='(org-lookup-first $2 '(remote(Table2A, @I$2..@II$2)) '(remote(Table2A, @I$8..@II$8)))
    #+TBLFM: $5=vsum($3..$4);f2


Bruno

Re: [the cryptic @@#$7]

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1571 bytes --]

>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:

> Hi Uwe,

> Uwe Brauer <oub@mat.ucm.es> writes:
>> Here is an example where the org-lookup-first method seems to fail

>> #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1))
>> '(remote(Table1A, @I$7..@II$7)))::$4='(org-lookup-first $2
>> '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A,
>> @I$7..@II$7)))::$5=vsum($3..$4);f2

> You forgot to update the column numbers: the names to look for are now in
> the column '2', and, the totals are now in column '8'. This should work:

>     #+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$2..@II$2)) '(remote(Table1A, @I$8..@II$8)))
>     #+TBLFM: $4='(org-lookup-first $2 '(remote(Table2A, @I$2..@II$2)) '(remote(Table2A, @I$8..@II$8)))

>     #+TBLFM: $5=vsum($3..$4);f2

Arg 😨


I even tried 
#+TBLFM: $3='(org-lookup-first $2 '(remote(Table1A, @I$1..@II$1))'(remote(Table1A, @I$8..@II$8)))
#+TBLFM:$4='(org-lookup-first $2 '(remote(Table2A, @I$1..@II$1)) '(remote(Table2A, @I$8..@II$8)))
#+TBLFM:$5=vsum($3..$4);f2


So I changed 

    1. $7-->$8 

    2. but *not* $1-->$2

Thanks very much

Uwe 



-- 
Warning: Content may be disturbing to some audiences
I strongly condemn Hamas terroristic attack on Israel.
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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: equivalent of VLOOKUP (in ods) to org-table

[Bruno Barbier]

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

>>>> "BB" == Bruno Barbier <brubar.cs@gmail.com> writes:
>> Did you check these lookup functions in the Org manual?
>>    (info "(org) Lookup functions")
>
>
> Yes of course, but I am unable to obtain the same result as I do using
> the remote call.

Did you try something like this ?

   #+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: table2   
   | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
   |--------+-----+-----+-----+-----+-----+--------|
   | Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
   | Miller |   2 |   1 |   6 |   9 |   3 |     21 |
   | Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
   #+TBLFM: $7=vsum($2..$6);f2

   | Name   |   | ResSh1 | ResSh2 | Total |
   |--------+---+--------+--------+-------|
   | Smith  | 4 |     22 |     33 |    59 |
   | Miller | 4 |     19 |     21 |    44 |
   | Wick   | 4 |     18 |     18 |    40 |
   #+TBLFM: $3='(org-lookup-first $1 '(remote(table1, @I$1..@II$1)) '(remote(table1, @I$7..@II$7)))
   #+TBLFM: $5=vsum($2..$4)

IIUC, it seems to be the result that you're expecting.

Bruno


>
> Uwe 
> -- 
> 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/

Re: equivalent of VLOOKUP (in ods) to org-table

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 722 bytes --]


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


>    #+TBLFM: $3='(org-lookup-first $1 '(remote(table1, @I$1..@II$1)) '(remote(table1, @I$7..@II$7)))

Oops no I tried 

$3='(org-lookup-first '(remote(table1, @1$7..@4$7)))

And that gave error

So thanks a lot

>    #+TBLFM: $5=vsum($2..$4)

> IIUC, it seems to be the result that you're expecting.

> 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/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]