跨多个工作表的多列的 Google Sheets VLOOKUP [英] Google Sheets VLOOKUP of multiple columns across multiple sheets

问题描述

我不是 100% 确定 VLOOKUP 是用于此的正确公式，但我尝试的是以下内容:

I am not 100% sure VLOOKUP is the correct formula to use for this, but what I was trying was the following:

=ArrayFormula(IF(len(B2:B),vlookup(B2:B,{‘New1′!B3:C;’New2′!B3:C;’New3’!B3:C},2,FALSE),""))

我想修改上述公式以执行以下操作.我有一个带有多个标签的 Google 表格.有一个选项卡将所有数据存储为元数据以填充其他 6 个选项卡.我将元数据拆分为 6 个选项卡，但希望避免在选项卡中复制数据.所以我想创建一个列，如果数据已经放置，它会向我显示当前放置数据的位置.下面是一个例子:

I wanted to modify the above formula to do the following. I have a Google Sheet with multiple tabs. There is a single tab that has all of the data stored as metadata to populate the other 6 tabs. I am splitting the metadata into the 6 tabs, but want to avoid duplicating the data in the tabs. So I wanted to create a column that would show me where the data is currently placed if it is placed already. Here is an example:

母版表有以下数据:

Name1, Phone1, Address1, E-mail1 
Name2, Phone2, Address2, E-mail2
Name3, Phone3, Address3, E-mail3 
Name4, Phone4, Address4, E-mail4

将有 4 个选项卡包含相同的列(姓名、电话、地址、电子邮件).我会将主表中的数据拆分为这 4 个选项卡.如果我将姓名 2、电话 2、地址 2、电子邮件 2"放在选项卡 3 中:我希望公式可以看到 VLOOKUP 中的整个数据块，而不仅仅是名称，如果是，在字段中显示选项卡 3".存在具有相同名称或地址的元数据，因此我希望 VLOOKUP 查看多个选项卡(Tab 1、Tab 2、Tab 3、Tab 4)并与多个列进行比较以匹配(名称、电话、地址、电子邮件都与主表中的相同)，如果找到匹配项...它将显示其所在选项卡的某种指示(选项卡 3").希望这是有道理的.

There will be 4 tabs where I have the same columns (Name, Phone, Address, E-mail). I will be splitting the data in the master sheet to these 4 tabs. If I place "Name2, Phone2, Address2, E-mail2" in Tab 3: I want the formula to see the entire chunk of data in the VLOOKUP, not just the name, and if it does, to display "Tab 3" in the field. There is metadata that has the same name or address, so I want the VLOOKUP to look across multiple tabs (Tab 1, Tab 2, Tab 3, Tab 4) and compare against multiple columns to match (Name, Phone, Address, E-mail are all the same as the ones in the master sheet) and if it finds that match...it will display some sort of indication of the tab it is on ("Tab 3"). Hopefully, this makes sense.

推荐答案

=ARRAYFORMULA(IFERROR(SUBSTITUTE(SPLIT(TRIM(
    TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(IF(LEN(A2:A), {
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet1!A2:D),,999^99)),1,0)), "Sheet1", ),
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet2!A2:D),,999^99)),1,0)), "Sheet2", ),
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet3!A2:D),,999^99)),1,0)), "Sheet3", ),
 IF(TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99))=IFERROR(VLOOKUP(
    TRANSPOSE(QUERY(TRANSPOSE(A2:D),,999^99)),
    TRANSPOSE(QUERY(TRANSPOSE(Sheet4!A2:D),,999^99)),1,0)), "Sheet4", )}, ),
 " ", "♦")),,999^99))), " "), "♦", " ")))

这篇关于跨多个工作表的多列的 Google Sheets VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！