没有数据的单元格的 Google Sheets Vlookup 结果中的“不匹配"消息 [英] 'No match' message in Google Sheets Vlookup results for cells with no data
问题描述
基于 (根据单元格引用从谷歌工作表中的另一个标签/工作表中搜索值)，我在 Google 工作表中有 2 个标签/工作表，一个从 Google 表单收集数据，另一个是搜索工作表.示例在这里(与用于上述问题的相同，但添加了数据):(https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=354631176)
Based on (Search a value from another tab/sheet in google sheets based on cell reference), I have 2 tabs/sheets in Google Sheets, one that gathers data from a Google Forms and the other is a Search sheet. The example is here (It's the same one used for the question mentioned above but with added data): (https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=354631176)
在搜索表的顶部，我有用户在另一个单元格中填充和向下的搜索字段我有一个搜索公式，该公式返回在 Sheet1 的同一行上找到的值的转置列表，与在 Sheet2 中找到的值相匹配.但是当它遇到工作表 1 中的空白单元格时，它要么停止查找值，要么给出下一个非空白单元格的值，而不管要返回的指示行如何.
On the top of the Search sheet, I have search fields that the user fills and down in another cell I have a search formula that returns a transposed list of the values found on the same row from Sheet1 matching the value found in Sheet2. But when it encounters a blank cell in sheet 1, it either stops looking for values or gives the value of the next non-blank cell regardless of the indicated rows to return.
说明:
在搜索表的顶部，我在用户填写的单元格 B3:F3 中有搜索字段(第 3 行)，在单元格 B8 中我有我的搜索公式:
On the top of the Search sheet I have search fields in cells B3:F3 that the user fills (row 3) and down in cell B8 I have my search formula:
=ARRAYFORMULA(
IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(B3, {'Respuestas de formulario 2'!AN:AN, 'Respuestas de formulario 2'!A:BN},
{24,3,21,23,13,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ),
IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(C3, {'Respuestas de formulario 2'!AK:AK, 'Respuestas de formulario 2'!A:BN},
{24,3,21,23,14,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ),
IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(D3, {'Respuestas de formulario 2'!AR:AR, 'Respuestas de formulario 2'!A:BN},
{24,3,20,23,13,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ),
IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(E3, {'Respuestas de formulario 2'!W:W, 'Respuestas de formulario 2'!A:BN},
{24,3,21,23,13,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ),
IF(F3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(F3, {'Respuestas de formulario 2'!Z:Z, 'Respuestas de formulario 2'!A:BN},
{24,3,21,23,13,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ), ))))))
<小时>
该公式搜索在 B3 到 F3(在工作表 2 中)中输入的值，以将它们与工作表 1(称为Respuestas de formulario 2")上的数据进行匹配，然后返回在该行中找到的值的垂直列表, 在每个结果后按特定顺序跳过一个单元格(这就是 {} 之间的数字不按顺序排列的原因).
The formula searches for the values entered in B3 to F3 (in Sheet 2) to match them against the data on Sheet 1 (called 'Respuestas de formulario 2') and then returns a vertical list of the values found in that row, skipping one cell after each result, in a certain order (that's why the numbers in between the {} are not in order).
然而，并非工作表 1 中的所有单元格都可能包含数据(例如，如果 Google 表单有一个跳过的部分，如如果‘是’，则转到第 2 部分；如果‘否"转到第 3 部分’).问题在于，当公式遇到空白单元格时，它似乎要么停止查找其他结果，要么跳转值并从具有值的新单元格返回内容.
However, Not all cells in Sheet 1 may contain data (For example, if the Google Forms has a skipped section Like those 'If 'yes', go to section 2; if 'no' go to section 3'). The problem is that when the formula encounters a blank cell it seems it either stops looking for the other results or jumps values and returns things from the new cell that has values.
我尝试用这个结束它，但没有用:
I tried ending it with this but it didn't work:
...{24,34,3,21,23,13,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ), ))))), "no match found")
<小时>
然后我尝试像这样在每个 IF 中添加未找到匹配项"，但也没有用:
Then I tried adding the "no match found" in each IF like this but didn't work either:
IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(B3, {'Respuestas de formulario 2'!AN:AN, 'Respuestas de formulario 2'!A:BN},
{24,3,21,23,13,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0,"no match found")), CHAR(10))), "♦", ),
<小时>
我希望在结果列的每个空白单元格中显示未找到匹配项"，而找到的值正是与 {} 中列出的行相对应的值订购.
I'm looking to present a "no match found" in each cell of the results column that is blank while the found values are those corresponding to the listed rows in the {} in exactly that order.
推荐答案
这是对问题的迂回解决方案，而不是对代码的修复.
This is a roundabout solution to the problem rather than a fix to the code.
我所做的是创建一个数据透视表，从 sheet1(带有 Google Forms 响应的那个)中提取数据，并用 N/A 填充所有空白单元格.我通过在 B1 中使用以下公式并将其一直拖到名为Pivot_sheet"的新工作表中的单元格 BN1 来做到这一点:
What I did was to create a pivot sheet that pulls the data from the sheet1 (The one with the Google Forms responses) and that fills out all blank cells with N/A . I did this by using the following formula in B1 and dragging it all the way to cell BN1 in the new sheet called 'Pivot_sheet':
=arrayformula(IF(LEN('Respuestas de formulario 2'!$A:$A), IF('Respuestas de formulario 2'!B:B<>"",'Respuestas de formulario 2'!B:B,"N/A"),""))
注意:在单元格 A1 中，将时间戳作为我仅使用的答案的参考:
NOTE: In cell A1 to have the timestamp as reference for answers I merely used:
=ArrayFormula('Sheet1'!A:A)
然后我将代码中搜索公式的引用从 'Respuestas de formulario 2'! 替换为新工作表的名称Pivot_sheet".像这样:
Then I replaced the reference in the code for the search formula from 'Respuestas de formulario 2'! to the name of the new sheet 'Pivot_sheet'. Like this:
... IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1,
VLOOKUP(B3, {'Pivot_sheet'!AN:AN, 'Pivot_sheet'!A:BN}, ...
这篇关于没有数据的单元格的 Google Sheets Vlookup 结果中的“不匹配"消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
