时间戳

首页 > 解决方案 > 没有数据的单元格的 Google 表格 Vlookup 结果中出现“不匹配”消息

filter - 没有数据的单元格的 Google 表格 Vlookup 结果中出现“不匹配”消息

问题描述

基于（根据单元格引用从谷歌表格中的另一个选项卡/表格中搜索值），我在 Google 表格中有 2 个选项卡/表格，一个从 Google 表单收集数据，另一个是搜索表格。示例在这里（与上述问题相同，但添加了数据）：（https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=354631176）

在搜索表的顶部，我有用户填写的搜索字段，并在另一个单元格中向下我有一个搜索公式，它返回在 Sheet1 的同一行中找到的值的转置列表，与在 Sheet2 中找到的值相匹配。但是，当它在工作表 1 中遇到空白单元格时，它要么停止查找值，要么给出下一个非空白单元格的值，而不考虑要返回的指示行。

解释：

在搜索表的顶部，我在单元格 B3:F3 中有用户填写的搜索字段（第 3 行），在单元格 B8 中我有我的搜索公式：

    =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”）相匹配，然后返回在该行中找到的值的垂直列表，跳过一个每个结果之后的单元格，按特定顺序排列（这就是 {} 之间的数字不按顺序排列的原因）。

但是，并非工作表 1 中的所有单元格都可能包含数据（例如，如果 Google 表单有一个跳过的部分，如“如果“是”，请转到第 2 部分；如果“否”，请转到第 3 部分）。问题在于，当公式遇到空白单元格时，它似乎要么停止寻找其他结果，要么跳转值并从具有值的新单元格返回内容。

我试着用这个结束它，但它没有用：

 ...{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 中添加“找不到匹配项”，但也没有用：

    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))), "♦", ),

我希望在结果列的每个单元格中显示一个“未找到匹配项”，该单元格是空白的，而找到的值是与按{}顺序列出的行相对应的值。

标签： filtergoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

解决方案

这是对问题的迂回解决方案，而不是对代码的修复。

我所做的是创建一个数据透视表，它从 sheet1（带有 Google Forms 响应的那个）中提取数据，并用 N/A 填写所有空白单元格。我通过在 B1 中使用以下公式并将其一直拖动到名为“Pivot_sheet”的新工作表中的单元格 BN1 来做到这一点：

    =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 中，将时间戳作为我仅使用的答案的参考：

    =ArrayFormula('Sheet1'!A:A)

然后我将搜索公式代码中的引用替换为'Respuestas de formulario 2'!新工作表“Pivot_sheet”的名称。像这样：

    ... IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
    VLOOKUP(B3, {'Pivot_sheet'!AN:AN, 'Pivot_sheet'!A:BN}, ...

推荐阅读