如何在Google表格和表格之间连接表格 [英] How to join tables between the sheet and query in Google Sheets

问题描述

在Sheet2中，我有以下列〜1000行

  [名称电子邮件地址电话产品] 
  

在Sheet3中，我有以下3000列的列

  [company first_name last_name full_name email amount] 
  

我需要加入工作表的结果，即
Sheet3电子邮件= Sheet2电子邮件地址

  first_name last_name（来自Sheet3），电子邮件地址（来自Sheet3），PRODUCT（来自Sheet2），金额（来自Sheet3）
  

我如何获得此功能，即使它是Google表格中的自定义功能也是如此！

我尝试过使用

  = QUERY（Sheet2！A1：D3000，Select B&''& C WHERE B ='& Sheet3！$ E2& '，0）
  

解决方案

样品片材。

您可以使用过滤 $ b $ p $ = FILTER（$ b code $ + $ code> vlookup $ b {Sales！B14：B，Sales！C14：C，Sales！E14：E，vlookup（Sales！E14：E，{People！B2：B，People！D2：D}，2，），
销售！F14：F}，销售！E14：E>）

FIlter 会将 vlookup 转换为 ArrayFormula 。这将从人员表中返回PRODUCT。

我重新命名表单以给它们更多有意义的名称。

Sales=Sheet3

People=Sheet2

In Sheet2 I have the following columns with ~1000 rows

[ NAME      EMAIL-ADDRESS       PHONE       PRODUCT ]

In Sheet3 I have the following columns with 3000 rows

[ company   first_name  last_name   full_name   email   amount ]

I need to get the the result by joining sheets, that is

Sheet3 email = Sheet2 EMAIL ADDRESS

first_name last_name (from Sheet3), email address (from Sheet3), PRODUCT (from Sheet2), amount (from Sheet3)

How do I get this, its ok even if it is a custom function in Google Sheets

I tried with

=QUERY(Sheet2!A1:D3000, "Select B & ' ' & C WHERE B='" & Sheet3!$E2 & "'", 0)

解决方案

Sample Sheet.

You may use filter + vlookup in a single formula:

=FILTER( {Sales!B14:B,Sales!C14:C,Sales!E14:E, vlookup(Sales!E14:E,{People!B2:B,People!D2:D},2,), Sales!F14:F},Sales!E14:E<>"")

FIlter will convert vlookup into ArrayFormula. This will return PRODUCT from sheet "People".

I renamed sheets to give them more meaningful names.

• "Sales" = "Sheet3"
• "People" = "Sheet2"

这篇关于如何在Google表格和表格之间连接表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！