使用Excel中的搜索功能检索匹配条件时出现问题 [英] Trouble retrieving the matched criteria using Search Function in Excel

问题描述

我正在成功使用下面的代码来测试表的列(task_short_text列)中的每个单元格，以查看它们中的每个单元格中是否都包含特定的单词.我在每个单元格中搜索的单词范围存储在另一张表中(参数！$ A $ 2:$ A $ 15).该公式的工作方式是返回TRUE或FALSE结果，例如:如果单元格中的字符串包含"Dog barks"，并且单词"dog"在搜索范围内，则输出为TRUE.

I am using the code below successfully to test every cell in a table's column (task_short_text column) to see if they contain a specific word within each one of them. The range of words I am searching within each cell is stored in another sheet (Parameters!$A$2:$A$15). The way this formula has worked is by returning a TRUE or FALSE result, Ex: If the string in the cell contains "The Dog barks" and the word "dog" is in the search range, the output will be TRUE.

我希望公式不返回TRUE，而是返回它在搜索范围内找到的单词.因此，例如，如果公式在参数"表中找到"dog"，我希望返回的是"dog"而不是TRUE.

Instead of returning TRUE, I want my formula to return the word it found in the search range. So, for example, if the formula finds "dog" in the Parameters sheet, I would like for the return to be "dog" instead of TRUE.

我尝试使用没有SUMPRODUCT和ISNUMBER函数的SEARCH函数，但这会产生错误(#VALUE！)

I have tried using the SEARCH function without the SUMPRODUCT and ISNUMBER functions, but this yields an error (#VALUE!)

=SUMPRODUCT(--ISNUMBER(SEARCH(Parameters!$A$2:$A$15,[@[task_short_text]])))>0

推荐答案

以下是解决此问题的简单示例:

Here is a simple example to approach this issue:

单元格B2中的公式:

=IFERROR(LOOKUP(1,1/COUNTIF(A2,"*"&$D$2:$D$4&"*"),$D$2:$D$4),"")

向下拖动.

这篇关于使用Excel中的搜索功能检索匹配条件时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！