根据单元格引用从Google工作表中的另一个标签/工作表中搜索值 [英] Search a value from another tab/sheet in google sheets based on cell reference

问题描述

我正在尝试在Google表格中创建一个搜索页面，在该页面中，用户在一个单元格中输入一个值，然后其他单元格根据在搜索单元格中输入的值自动填充另一标签/工作表中的值(s).

I'm trying to create a search page in google sheets where the user enters a value in one cell(s) and then other cells auto-populate with values from another tab/sheet based on the value entered in the search cell(s).

我正在尝试在数组中使用VLOOKUP，但是它给出了错误或N/A.

I'm trying to use VLOOKUP up in an array but it gives either error or N/A.

我有"sheet1"，其中包含来自Google表单(从A到BN的列)的数据，并且我试图在"Sheet2"中创建一个搜索页面，用户可以在上面搜索例如ID号，以及我想从"sheet1"中获取与在"sheet1"的同一行上写入的ID号匹配的列的值.

I have 'sheet1' with data from a google forms (columns from A to BN) and I'm trying to make a search page in 'Sheet2' where on top the user can write for example the ID-number, and I want to grab the value of a column from 'sheet1' that matches the ID-number written on the same row for 'sheet1'.

我已经检查过了:

• VLOOKUP从另一个Google表格中的表格

如何获取价值取决于电子表格中的列表选择?

Google表格-ARRAYFORMULA内的VLOOKUP

还有其他人，但我不需要任何脚本.

There are others but I'm not looking for any scripts.

以简单的形式，公式将是:

In simple form the formula would be:

=VLOOKUP(B3,'sheet1'!A:BN,40,False)

但是因为我的列太多了，所以我也需要获取其他值:

But because I have too many columns and I'll be needing to grab other values too I'm using:

=ArrayFormula(IF(LEN(B3),VLOOKUP(B3,{'sheet1'!A:BN},{iferror(match("ID-number",'sheet1'!A1:BN1,0),"No match")},FALSE),""))

说明:

B3是用户输入ID号值的单元格

B3 is the cell where the user inputs the id number value

然后...:

{'sheet1'!A:BN},{iferror(match("ID-number",'sheet1'!A1:BN1,0),"No match")}

是我要查找的值标题的列号的地方，在这种情况下，它是AN，它将是第40列，但也可以是其他任何列.

is where I'm looking for the number of the column of the title of value I'm looking for, in this case, it is AN, which would be the column 40 but it could be any other.

然后ArrayFormula是因为sheet1是来自Google表单的数据，该数据正在使用中，并且行数不断增加.

And then the ArrayFormula is because sheet1 is data from a Google form that is on use and increasing rows constantly.

它给我一个错误#N/A，因为它找不到在范围内(从A到BN的所有列中都有值的选项卡sheet1)在范围内的值(在工作表2中的B3中输入)

It gives me an error of #N/A because it doesn't find the value (input in B3 in sheet 2) in the range (tab sheet1 in all the columns from A to BN that have values)

带有工作答案的示例表在[here]: ( https://docs.google.com/spreadsheets/d/1qLcJd4L4V7L1D/edit?usp =共享)

注意::要搜索/返回的值在工作表1的同一行上，当输入新的搜索时，只有该行会更改.

NOTE: The values to search/return are on the same row in sheet 1 and when a new search is entered then only the row changes.

推荐答案

=ARRAYFORMULA(
 IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(B3,            data!A:E,  {1,2,3,5}, 0)), CHAR(10))), "♦", ),
 IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(C3, {data!C:C, data!A:E}, {2,3,4,6}, 0)), CHAR(10))), "♦", ), 
 IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(D3, {data!E:E, data!A:E}, {2,3,4,6}, 0)), CHAR(10))), "♦", ), ))))

=IFERROR(ARRAYFORMULA(
 IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(B3, {data!AN:AN, data!A:BN},  {41,38,19,11,55}, 0)), CHAR(10))), "♦", ),
 IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(C3, {data!AK:AK, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), "♦", ), 
 IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(E3, {data!BJ:BJ, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), "♦", ), 
 IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(D3, {data!R:R, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), "♦", ), ))))), "no match found")

这篇关于根据单元格引用从Google工作表中的另一个标签/工作表中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！