多个工作表中的多个列的Google表格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个选项卡.如果在选项卡3中放置"Name2，Phone2，Address2，E-mail2"，我希望公式可以看到VLOOKUP中的整个数据块，而不仅是名称，如果显示，则显示制表3" "在该领域.存在具有相同名称或地址的元数据，因此我希望VLOOKUP跨多个选项卡(选项卡1，选项卡2，选项卡3，选项卡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表格VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！