From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp10.migadu.com ([2001:41d0:403:478a::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms9.migadu.com with LMTPS id oFQaGYHnImVSUgEAG6o9tA:P1 (envelope-from ) for ; Sun, 08 Oct 2023 19:31:45 +0200 Received: from aspmx1.migadu.com ([2001:41d0:403:478a::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp10.migadu.com with LMTPS id oFQaGYHnImVSUgEAG6o9tA (envelope-from ) for ; Sun, 08 Oct 2023 19:31:45 +0200 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id E20276B123 for ; Sun, 8 Oct 2023 19:31:44 +0200 (CEST) Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20230601 header.b=hNvrTZjn; dmarc=pass (policy=none) header.from=gmail.com; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1696786305; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references:list-id:list-help: list-unsubscribe:list-subscribe:list-post:dkim-signature; bh=FwFUol0VtmYJLwpJGjRY6Ieybhs5VCwLqp7JrsOSLAc=; b=UvLziQP6j+ck3w02FwqLHfuqDJ++SJY2iRhur7Bdrkt6H3M5Hmyy/GZ/ExGzD6tiniG7sy igkaimBGU40qkV3p4vF5i0uft4JkSQl0WD8LnfdWNOE29o6G0FRvQ6f+gsOSGG50ICqQhr gjFJq9RzRI2Iyqv/gm4WvVABkXcvZTYIgKEmK5HoJ/WAq41u80rZsJvFaqkMeZ6rpmrJ7p 5VKIkLPi5PQk6pMZq9lcQ7TydEzDUoZ5Eh4Q7IrIXVR3dumwmbHnrNTfe308LqY4EGcAic O+mI6hZVvzKursyl0fO1TVouWGGUDa9vbKdXBjN3n5spou7aHpJryBgCBaFBwQ== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1696786305; a=rsa-sha256; cv=none; b=gqUSz2a1mFCTsFDVQvCLVwo/AT5NgZurfSWAAyCq/XkU5oniAezmAaXwbIoYjykfmeT6LO 24dZyOan53TQWcVQ6xq5iPUIdo13ixr7U/o1kr/vAOs48i+mvYvfaqlklIDRw1TYZYx465 KG4aANUgIglI848Bkv4nOEbE10u9hZOTxe1//V8EnVSM9TkvtLWGAk5myICXcMD0FlMayh FFS+8H33NSccIFRHuXx8L3Sqqnm40MGUb1AH4ZmUPiRjzqkHQ6oulf4iSOTgh+v4NMRg5D X/QKlALYqtugGjy3iuXcDWFVzJawwpgvJLOfLwCsI86A8rEvmrGTyLDqvBP/IQ== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20230601 header.b=hNvrTZjn; dmarc=pass (policy=none) header.from=gmail.com; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1qpXc6-0004EX-Lk; Sun, 08 Oct 2023 13:30:46 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qpXc5-0004E6-BH for emacs-orgmode@gnu.org; Sun, 08 Oct 2023 13:30:45 -0400 Received: from mail-wm1-x330.google.com ([2a00:1450:4864:20::330]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1qpXc2-0002X6-El for emacs-orgmode@gnu.org; Sun, 08 Oct 2023 13:30:44 -0400 Received: by mail-wm1-x330.google.com with SMTP id 5b1f17b1804b1-4060b623e64so22484505e9.0 for ; Sun, 08 Oct 2023 10:30:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696786239; x=1697391039; darn=gnu.org; h=content-transfer-encoding:mime-version:date:references:in-reply-to :subject:to:from:message-id:from:to:cc:subject:date:message-id :reply-to; bh=FwFUol0VtmYJLwpJGjRY6Ieybhs5VCwLqp7JrsOSLAc=; b=hNvrTZjn+TS3Hr1+YSnSXUimFNwkcbv9SDNOempxCoRKgmMO/yFeWC82TP2IFmaI3t h3/OPLvLqxedORE4a3XbP8C8hCHAYpGa74QHErDxIfix1B9MQd38lHJM/xP7s3QnuZd7 X0M7yl4qytjqKft11VGFVZDioWQwHfjptPSk/fHnXTle0QJ3EHIWbV7wyOlJ7tkXcYw4 ZbxTo4xIP6sDCuzVZ9l4W5gspUB4xa6PIdtSulgLunXSzjTuAPvfipUVAYxZ0qGIoEzn 5gj9z0CcZvJJf21tGrWXprtDjmTev7M5xw990YGhcRvY/eIczKC9+LwGH4dUb0Et215+ KhUw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696786239; x=1697391039; h=content-transfer-encoding:mime-version:date:references:in-reply-to :subject:to:from:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=FwFUol0VtmYJLwpJGjRY6Ieybhs5VCwLqp7JrsOSLAc=; b=ZybQwYBEDTBb2EGThy7+u6Dxy/3Wi3ox3HlqAxuo/ZW0I9eHnqO1W6zMNv3C7VK/bY RLHHInFhtQstjDscz2Zia6vS7qchf1nvSsSgCuWRIKLUma9aCPha/7ot4De6OpNW4v3h pEZZsUQ/v0xTgS4YaCr23D+ivkl3qP1BNGHTei18kOgRerpwNa32fweMujAtmgFyptGF SJ+xFLbUFvUITCrPDP+aMJ2rsqvccfu9tsqWtikIw52OT9liFlHAg8uMsjESDLjbVUjg sQYNWRjquV2gMt1nP3XToKF3a2sDX2XGX3GBgAS7H01YTdrUdenTVGAdNn1wdaIobMhh 4aFg== X-Gm-Message-State: AOJu0Yy3JpEzyyRvrxUuxJKoRmIvp+9vMAN/aV21uwTTEDnrJaN1xPSl LtoZVlwemDbrUsqObQYdc/0= X-Google-Smtp-Source: AGHT+IE2D3Qrgtpv+puEot0xMGMOA3Y/2JWNhJxlF7jBPwP7YAjkyYGN1mVHWJNY7XgsSueG4/WMkg== X-Received: by 2002:a1c:7505:0:b0:405:3f06:d2ef with SMTP id o5-20020a1c7505000000b004053f06d2efmr8390252wmc.4.1696786238504; Sun, 08 Oct 2023 10:30:38 -0700 (PDT) Received: from keynux ([2a01:e0a:505:3460:169:7511:f49a:58eb]) by smtp.gmail.com with ESMTPSA id l17-20020a1ced11000000b0040588d85b3asm11186308wmh.15.2023.10.08.10.30.37 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sun, 08 Oct 2023 10:30:38 -0700 (PDT) Message-ID: <6522e73e.1c0a0220.17022.1dfd@mx.google.com> Received: by keynux (sSMTP sendmail emulation); Sun, 08 Oct 2023 19:30:36 +0200 From: Bruno Barbier To: Uwe Brauer , emacs-orgmode@gnu.org Subject: Re: [the cryptic @@#$7] (was: equivalent of VLOOKUP (in ods) to org-table) In-Reply-To: <87v8bhgidc.fsf_-_@mat.ucm.es> References: <87h6n1i84d.fsf@mat.ucm.es> <6522dbff.df0a0220.53465.d25c@mx.google.com> <871qe5hxed.fsf@mat.ucm.es> <87v8bhgidc.fsf_-_@mat.ucm.es> Date: Sun, 08 Oct 2023 19:30:36 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Received-SPF: pass client-ip=2a00:1450:4864:20::330; envelope-from=brubar.cs@gmail.com; helo=mail-wm1-x330.google.com X-Spam_score_int: -20 X-Spam_score: -2.1 X-Spam_bar: -- X-Spam_report: (-2.1 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, FREEMAIL_FROM=0.001, MSGID_FROM_MTA_HEADER=0.001, RCVD_IN_DNSWL_NONE=-0.0001, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: emacs-orgmode-bounces+larch=yhetil.org@gnu.org X-Migadu-Flow: FLOW_IN X-Migadu-Country: US X-Migadu-Scanner: mx0.migadu.com X-Migadu-Spam-Score: -7.04 X-Spam-Score: -7.04 X-Migadu-Queue-Id: E20276B123 X-TUID: 4o6NW0PVU12k Uwe Brauer writes: >>>> "UB" =3D=3D Uwe Brauer writes: > >>>> "BB" =3D=3D Bruno Barbier writes: >>> Hi Uwe, > >>> Uwe Brauer 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=3Dvsum($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=3Dremote(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" "=E2=80=98@#=E2=80=99 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=3Dremote(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=3Dremote(table1,@@#$7) #+TBLFM: @2$4..@>$4=3D@# #+TBLFM: @2$5..@>$5=3D'(concat "@" (format "%d" @#) "$" "7") Bruno > > > > --=20 > 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.=20 > I support the NATO membership of Ukraine. > I support the EU membership of Ukraine.=20 > https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation= -view/