Google Sheets Vlookup结果中没有数据的单元格出现"No match"消息 [英] 'No match' message in Google Sheets Vlookup results for cells with no data
问题描述
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.
推荐答案
这是针对此问题的round回解决方案，而不是对代码的修复.
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结果中没有数据的单元格出现"No match"消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
