根据单元格引用从谷歌工作表中的另一个标签/工作表中搜索值 [英] Search a value from another tab/sheet in google sheets based on cell reference
问题描述
我正在尝试在 google 表格中创建一个搜索页面，其中用户在一个或多个单元格中输入一个值，然后其他单元格根据在搜索单元格中输入的值自动填充另一个选项卡/工作表中的值(s).
我试图在数组中使用 VLOOKUP 但它给出错误或 N/A.
我有一个包含来自谷歌表单的数据的sheet1"(从 A 到 BN 的列)，我正在尝试在Sheet2"中创建一个搜索页面，用户可以在其中写入例如 ID 号，以及我想从sheet1"中获取与sheet1"在同一行上写的 ID 号相匹配的列的值.
我已经检查过:
更新:
=IFERROR(数组公式(IF(B3<>"" ;,替代(移调(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<>"" ;,替代(移调(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<>"" ;,替代(移调(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<>"" ;,替代(移调(SPLIT(TEXTJOIN(CHAR(10)及;"♦"&安培; CHAR(10)及;"♦"，1，VLOOKUP(D3, {data!R:R, data!A:BN}, {41,38,19,11,55}, 0)), CHAR(10))), ♦", ), )))))、未找到匹配项")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).
I'm trying to use
VLOOKUPup in an array but it gives either error orN/A.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'.
I've already checked:
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),""))Explanation:
B3 is the cell where the user inputs the id number value
Then ...:
{'sheet1'!A:BN},{iferror(match("ID-number",'sheet1'!A1:BN1,0),"No match")}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.
And then the ArrayFormula is because sheet1 is data from a Google form that is on use and increasing rows constantly.
It gives me an error of
#N/Abecause 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)Example sheet with working answer is [here]: (https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit?usp=sharing)
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))), "♦", ), ))))
UPDATE:
=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")这篇关于根据单元格引用从谷歌工作表中的另一个标签/工作表中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
