VLOOKUP在Google表格中的另一张表格左侧 [英] VLOOKUP to the left from another sheet in Google Sheets

问题描述

我有一个电子表格，该电子表格可以从Google表格中的我自己的电子表格进行外部管理.我试图将数据从该外部工作表中提取到我的工作表中，所以我有一个集中的位置来显示与我相关的任务项.这是我认为可行的公式:

I have a spreadsheet that is managed externally to my own spreadsheet in Google Sheets. I am trying to pull in data to my sheet from that external sheet so I have a centralized location to display task items that pertain to me. Here is the formula that I thought would work:

=VLOOKUP("My Name", IMPORTRANGE("https://linkto.othersheet.com", {"Tab1!$G$1:$G$999","Tab2!$A$1:$A$999"}),2,FALSE)

该公式应使用IMPORTRANGE从外部工作表中抽取2列(或多列).然后根据我的名字"进行VLOOKUP，在G列中找到所有匹配项，并在相应的A列中进行回答.

The formula should use IMPORTRANGE to pull in 2 of the columns (or multiple columns) from the external sheet. Then do a VLOOKUP based on "My Name", find any matches in column G and reply with the value in the corresponding column A.

如果将索引设置为1，它将返回G列的值.如果将索引设置为2，则会出现超出范围的错误.我也希望它显示所有结果，而不仅仅是第一个.因此，如果有多个匹配的结果，我希望所有结果都单独显示在一行中.

If I set the index to 1, it returns the value of column G just fine. If I set the index to 2, it gives me an out of bounds error. I also want it to show all results, not just the first one. So if there are multiple results that match, I want all of the results to show in a row separately.

这是公式工作的示例表:

Here is an example sheet for the formula work: https://docs.google.com/spreadsheets/d/13R5VEv3cyZ3vJgb2S90xDxvpXyVV38yZXO8zIAd9OR4/edit?usp=sharing

这是外部源数据的示例表: https://docs.google.com/电子表格/d/13P-nNONZuesnmByZCp6-zmIyMVJpm__nvsz3w-ItwtQ/edit?usp = sharing

Here is the example sheet for the external source data: https://docs.google.com/spreadsheets/d/13P-nNONZuesnmByZCp6-zmIyMVJpm__nvsz3w-ItwtQ/edit?usp=sharing

推荐答案

不确定为什么需要VLOOKUP...

not sure why you need VLOOKUP tho...

=QUERY(IMPORTRANGE("13P-nNONZuesnmByZCp6-zmIyMVJpm__nvsz3w-ItwtQ", "Sheet1!A2:E"), 
 "select Col1,Col2 where Col4 = 'Engineer'")

这篇关于VLOOKUP在Google表格中的另一张表格左侧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！