查看列中是否存在任何值（excel） [英] See if any values in array exist in a column (excel)

问题描述

我有一个30列和800行的数组（Sheet 1），每个单元都有文本或空白。我想检查这个数组中的任何值（特别是逐行）是否存在于列（Custody List Hedge）中：

示例：

表1：

  ASKF AQA6 
 ARO3 ASKD AQA5 
 ASKA ARMA ARNA ARMB ARBA 
 ARMV ARN6 ARNS ARO6 AQFS 
 ARQ4 ARMJ ARN4 ARNJ ARO4 ARBJ 
  

托管列表对冲：

MCH基金号

  BWTZ 
 AEGF 
 AEGH 
 AEGJ 
 AEGL 
 AEGM 
 AEGP 
  

我已经尝试过计数，匹配索引，vlookups，但没有什么似乎真的正常工作。我不想使用VBA。我知道这可以用循环完成，但是必须设置一个功能来实现。到目前为止，我尝试过这个，但我不认为它每次都工作，不知道为什么...

= INDEX（'Sheet 1'！D：AE，MATCH 保管清单 - 对冲！A3，'托管清单 - 对冲'！A：A，0），1）

解决方案

为此，我将文本在 A1 中搜索到 F5 ，并在 A11 中搜索的代码 A17

首先，我们需要 FIND 字符串中的文本： FIND $ A $ 11：$ A $ 17，A1：F1）

接下来，我们需要认识到我们需要这个数组公式否则它只会检查A11和A1）

这是通过使用 CTRL + SHIFT + ENTER 完成的完成公式

这样会产生一个看起来像 #VALUE！;＃VALUE！;＃VALUE！;＃VALUE！;＃VALUE！; 1

现在我们把它转换成告诉我们文本是否被发现的东西

这些值是转换的由ISERROR进入 TRUE; TRUE; TRUE; TRUE; TRUE; FALSE

然后我们交换值，所以如果没有发现，我们有TRUE，如果没有，则为FALSE， NOT ，将所有内容转换为 FALSE; FALSE; FALSE; FALSE; FALSE; TRUE

接下来，我们将它们转换为可以使用 - 添加的数字。这是将TRUE转换为1并将FALSE转换为0的技巧。我们然后具有 0; 0; 0; 0; 0; 1

最后，我们SUM数字，然后告诉我们您正在搜索的字符串中找到了多少个单词

这将构建最终的公式如下：

  {= SUM（ - （NOT（ISERROR（FIND（$ A $ 11： A $ 17，A1：F1）））））} 
  

（大括号， {} 会让你知道你输入了一个数组公式）

然后你可以用 0 没有找到， 0 找到

I have an array of 30 columns and 800 rows (Sheet 1), each cell has either text or is blank. I want to check if ANY values in this array (specificially row by row) exist in a column (Custody List Hedge):

Example:

Sheet 1:

 ASKF    AQA6               
 ARO3    ASKD    AQA5           
 ASKA    ARMA    ARNA    ARMB    ARBA   
 ARMV    ARN6    ARNS    ARO6    AQFS   
 ARQ4    ARMJ    ARN4    ARNJ    ARO4    ARBJ

Custody List Hedge:

MCH Fund Number

BWTZ
AEGF
AEGH
AEGJ
AEGL
AEGM
AEGP

I've tried count, match index, vlookups but nothing seems to really be working. I don't want to use VBA. I know this could be done with loops but there has to be a function set to do this. So far I tried this but I don't think it's working everytime, not sure why...

=INDEX('Sheet 1'!D:AE,MATCH('Custody list - Hedge'!A3,'Custody list - Hedge'!A:A,0),1)

解决方案

For this, I put the text to search in A1 to F5, and the codes to search for in A11 to A17

First, we need to FIND the text in the string: FIND($A$11:$A$17,A1:F1)

Next, we need to realize we need this needs to be an array formula (else it will only check A11 and A1)
This is done by using CTRL+SHIFT+ENTER when we actually finish construction the formula
This results in a list that looks like #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1

Now we convert this into something that tells us if the text was found
The values are converted by the ISERROR into TRUE;TRUE;TRUE;TRUE;TRUE;FALSE

Then we swap the values, so we have TRUE if found and FALSE if not, with NOT, which inverts everything to FALSE;FALSE;FALSE;FALSE;FALSE;TRUE

Next we convert those to numbers we can add with --. This is a trick to conver TRUE into 1 and FALSE into 0. We then have 0;0;0;0;0;1

Finally, we SUM the numbers, which will then tell us how many words in the list were found in the string you are searching

This will build a final formula that looks like this:

{=SUM(--(NOT(ISERROR(FIND($A$11:$A$17,A1:F1)))))}

(The curly brackets, {} will let you know you entered an array formula)

You can then test this against 0 for nothing found, and >0 for some words found

这篇关于查看列中是否存在任何值（excel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！