Excel从单元格内搜索值的范围查找单元格 [英] Excel find cells from range where search value is within the cell

问题描述

再次，我正在努力寻找我在Google上的答案，但我确信它必须存在。对不起，如果我作为一个新手遇到了：我是Excel的似乎！

Once again I'm struggling to find my answer on Google, but I'm sure it must exist. Sorry if I come across as a novice: I am when it comes to Excel it seems!

我想要做的是告诉它搜索一个范围，然后在该范围内查找包含在我的搜索功能中的文本的单元格。我不需要任何结果，除了TRUE或> 1（如果它匹配，显然）。

What I'd like to be able to do is tell it to search a range, then find cells within that range that contain text that is in my search function. I don't need any kind of result from it other than either TRUE or >1 (if it matches, obviously).

在这种情况下，我正在搜索一个范围这是由几年组成，每个单元格一年，并尝试在该范围内找到包含一年以内的单元格的单元格。

In this instance I'm searching a RANGE that is comprised of years, one year in each cell, and trying to find cells in that range that contain a year from a list of years that are all in one cell.

基本上我想使用类似于我想的搜索功能的功能。

Basically I'm wanting to use a function similar to the Search function I think.

=SEARCH(text to find, find within text)

但是，我希望它做相反的事情，找到包含一些源单元格中的文本：

However, I'd like it to do the opposite and find cells that contain some of the text within the source cell:

=SEARCH(find within text, text to find)

或者，更具体地说，

=SEARCH("2001,2002,2003,2004,2005", "2003")

是否可能没有使用宏？如果有可能，我宁愿避免。所有单元格都被格式化为文本。

Is this possible without the use of a Macro? I'd prefer to avoid it if at all possible. All cells in question are formatted as Text.

我一直在尝试使用COUNTIF，但是它与我所需要的相反。

I have been experimenting with COUNTIF, but again it works in the reverse of what I need.

对不起，如果这个问题不清楚。
希望有人可以帮助，谢谢提前。

Sorry if this question is unclear. Hope someone can help, thanks in advance.

Joe

推荐答案

我确定有一个更好的方法，但如果我正确理解，可以尝试 SUM 结合数组公式（输入 Ctrl + Shift + Enter ）

I'm sure there is a better way, but if I'm understanding correctly, you could try SUM in combination with an array formula (enter with Ctrl+Shift+Enter):

=IF(SUM(IFERROR(FIND(A1:E1,G1),0))>0, "FOUND", "NOT FOUND")

这里， A1：E1 包含单个年份， G1 包含单个单元格的年份。在范围内的每个单元格上运行 FIND ，如果在目标单元格中​​找到匹配并返回0，则返回一个位置（ IFERROR 是一个2007年的功能 - 如果你没有Excel 2007，我们可以重写）。然后，您将 FIND 函数的结果相加，如果大于0，则表示您在某处找到匹配。

Here, A1:E1 contained the individual years and G1 contained the single cell of years. This runs FIND on each cell in the range, returning a position if it finds a match in the target cell and returning 0 if not (IFERROR is a 2007 function - if you don't have Excel 2007, we can rewrite). You then sum the results of the FIND function and if it is greater than 0, it means you found a match somewhere.

这篇关于Excel从单元格内搜索值的范围查找单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！