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 8BmHADjcImURxgAAG6o9tA:P1 (envelope-from ) for ; Sun, 08 Oct 2023 18:43:36 +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 8BmHADjcImURxgAAG6o9tA (envelope-from ) for ; Sun, 08 Oct 2023 18:43:36 +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 9611A65D2B for ; Sun, 8 Oct 2023 18:43:35 +0200 (CEST) Authentication-Results: aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20230601 header.b=TxNKO8Sw; 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=1696783415; 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:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post:dkim-signature; bh=GqKF+D1ZhIB7XTyQdam9IPbD42U6DG/yRCc5HAHiQL8=; b=ueZoPmV6lblNXSyBz4Ak4ndpv/EUGJoxWJajUpT5jiLRMRMqyfJ0dkFDIPWqJaZqz39RsU Z3RQjWWvawr0dfyTlQTkFCB+tYW/AKLLLp894bzzP+Wq/xaGaJN5Sbd8XP7k+4iB+CctXJ EMTYhyQwhsVfrCDJvsktvfxJo4gNOfHUgE12IUeRRz5HcvDS6QJh5b3Z5k5MBiBsfmybXu sj/6C9kF6Cf257Lht8Gkyc2PfIrirUEK/aQyYJ6Ig1GeSobkRJ41K0vlJc7uR7fm1W8Nki 0OysqfYjOE910p8Gal7XZ3e4urAhBjcKJn11CUhuGEY4t2aLYWIAueoB3Kwngw== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1696783415; a=rsa-sha256; cv=none; b=iQyvxIz8bIFAiIuLHlD8rDQP7JETyinEPiEQYwlnusJ0Ng8gRmU1rDqsY9n+Gdde7bmrsR B4ixi2xz329XRnRqa/2IEx+NcsWnbhRWh908XACjvrcrujYNbfxePglsJLLVz/t3kMdo/F TetP8yaG7G0x5n3+HruWy4bxoJnAvuXcegO7lIHP/O52XZ53NA9Rbazw8thMzCw5JZJC+c gSdzCifmrV6smCwEZGEx1UtnHPXlElGqrHXU+Fl6YpvzWlvQ8eEeHUVKcpn2s9h+MNcAid 6P1gEGZmhCPwECWwiEzvAN8Dnb2fRtsPzRNfTgUMc8Q8qboSndsBmg6rtPJ/wg== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=pass header.d=gmail.com header.s=20230601 header.b=TxNKO8Sw; 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 1qpWrd-00049E-JY; Sun, 08 Oct 2023 12:42:45 -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 1qpWrb-00048u-HM for emacs-orgmode@gnu.org; Sun, 08 Oct 2023 12:42:43 -0400 Received: from mail-wr1-x42d.google.com ([2a00:1450:4864:20::42d]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1qpWrZ-0000Nn-Jj for emacs-orgmode@gnu.org; Sun, 08 Oct 2023 12:42:43 -0400 Received: by mail-wr1-x42d.google.com with SMTP id ffacd0b85a97d-32799639a2aso3753426f8f.3 for ; Sun, 08 Oct 2023 09:42:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696783359; x=1697388159; darn=gnu.org; h=mime-version:date:references:in-reply-to:subject:to:from:message-id :from:to:cc:subject:date:message-id:reply-to; bh=GqKF+D1ZhIB7XTyQdam9IPbD42U6DG/yRCc5HAHiQL8=; b=TxNKO8SwHiJM1pJzWlePP61Ca394t5/OAruY2Nio+aVf6++h7UZA0DTwmOET+MrCTY E+CSqwE7iPy9IEgBzWwiqV78kdgDl0XB3GrcKLHQkuwbD2KxLBIX8zHppECQrjVSbGlW 5Tk6VjSMp+hXjnCa3C8SSO0QzHDSuhwqjakcnZD4KxinnzIaImvDb2mVN92Kj/Yg/RLR ZVnU7nYt8qWXjkjQ02+kAi++Qd1YDjKW4nLSEomgIQn6Ax+LF0P/XtuRZX7cYL2Ero+y EuQOmj/6ZOebf2695xbGrYc2TOAYv5Gy7VMn+bsr29pYVcvvoUusHeDoIbGmTgQa7zF4 yCHw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696783359; x=1697388159; h=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=GqKF+D1ZhIB7XTyQdam9IPbD42U6DG/yRCc5HAHiQL8=; b=E18v3+TZyusPjcKqVwPnEdrkhpsKsth4W8YjbLJ8iwo1K35j03X+Dzld1NRhbH0Mlf HH7Y/QDp4HeWFnoQFAVn6qScb/3khVLI5OoNxzFR1ulaveeL457DSU/6tMgRx9/OXRRa FxzaSjR64w14bLTovtfJpQctDMxhwaRdggvB8uE8VlApYH8r2CceZx1IsIxoUVtuoj0B Jds15+T/X4FVxISJVLZerVwx9ELBnTxSVtVGLEy6cItTmcDxBVr5UEdzB7I4pqxzHlDX Db1HRpHn4H8SsnKGcsmf9Jlt43M29B5jyuLSPOW4lE918M7zTz6txpUx5u3u8RvFsjxy XOPA== X-Gm-Message-State: AOJu0YxW4wLfdLJ2ayDPEDwFDjTRX//yPY3HMJ2vH91UQYcNweFxc6HJ Q5TkXUb7BiiQWn20/gqhISU= X-Google-Smtp-Source: AGHT+IH2y1RQuXibkRSgPaXx8IYVFqVnRwpWs/jO2+qH/3569uTdGDwo7b1FpMcy92xzkZLskUgnFA== X-Received: by 2002:a5d:574f:0:b0:321:63d0:1f0e with SMTP id q15-20020a5d574f000000b0032163d01f0emr10914854wrw.20.1696783359440; Sun, 08 Oct 2023 09:42:39 -0700 (PDT) Received: from keynux ([2a01:e0a:505:3460:169:7511:f49a:58eb]) by smtp.gmail.com with ESMTPSA id z3-20020adfec83000000b0032327b70ef6sm7263310wrn.70.2023.10.08.09.42.38 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sun, 08 Oct 2023 09:42:39 -0700 (PDT) Message-ID: <6522dbff.df0a0220.53465.d25c@mx.google.com> Received: by keynux (sSMTP sendmail emulation); Sun, 08 Oct 2023 18:42:37 +0200 From: Bruno Barbier To: Uwe Brauer , emacs-orgmode@gnu.org Subject: Re: equivalent of VLOOKUP (in ods) to org-table In-Reply-To: <87h6n1i84d.fsf@mat.ucm.es> References: <87h6n1i84d.fsf@mat.ucm.es> Date: Sun, 08 Oct 2023 18:42:37 +0200 MIME-Version: 1.0 Content-Type: text/plain Received-SPF: pass client-ip=2a00:1450:4864:20::42d; envelope-from=brubar.cs@gmail.com; helo=mail-wr1-x42d.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: -5.04 X-Spam-Score: -5.04 X-Migadu-Queue-Id: 9611A65D2B X-TUID: F8/DeS76FfI8 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") 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/