问题描述
在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)
解决方案 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表格和表格之间连接表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!