Excel 2016中SEARCH函数返回数组时为何未显示#VALUE!？

场景1（合理结果） #

B2单元格公式为=SEARCH(C2:C3,A1)，返回#VALUE!，此为合理结果：C2为空值，SEARCH无法在A1中找到匹配，生成的数组第一个元素即为错误值，单个单元格直接显示该错误。

场景2（异常表现） #

B2单元格公式为=SEARCH(C1:C2,A1)，本该返回数组[2, #VALUE!]（C1的"Tom"在A1中匹配位置为2，C2的"Jerry"在A1中无匹配返回错误），但单个单元格仅显示第一个元素2，与=SEARCH(C1,A1)结果一致，未出现#VALUE!。

原因分析 #

在Excel 2016这类旧版本中，未通过Ctrl+Shift+Enter确认的数组公式，在单个单元格中只会返回数组的第一个元素：

• 场景1中，SEARCH(C2:C3,A1)生成的数组是[#VALUE!, #VALUE!]，第一个元素为错误值，因此单元格显示#VALUE!；
• 场景2中，数组第一个元素是有效匹配位置2，因此单元格直接显示该值，忽略后续的错误元素。

而ISNUMBER、SUMPRODUCT这类函数会遍历整个数组进行计算，不受单个单元格显示逻辑的影响，因此能得到正确结果。

内容的提问来源于stack exchange，提问作者HelpMePlease