Excel - 匹配包含与列表完全匹配的单元格 [英] Excel - Match cells that contains exact match from list

问题描述

在我的第一页中，我有一个列表，其中包含A列中的以下10个项目（一个在行：A2-A11）：

  egg 
 bootleggers egg 
蛋是好的
蛋和火腿
鸡蛋和火腿
 bootleggers 
蛋壳
 veggie 
雷鬼
  

我有一个第二张表其中包含一个表（表名： good_food ），其中包含以下项目：

  egg 
 ham 
  

我想先了解中的哪些项目

这几乎适用于以下公式：

  = SUMPRODUCT（ - -ISNUMBER（SEARCH（good_food; A2）））> 0 
  

尽管所有项目都返回TRUE ：

  egg TRUE 
 bootleggers egg TRUE 
 egg is good TRUE 
 eggisgood TRUE 
蛋和火腿TRUE 
鸡蛋和火腿TRUE 
 bootleggers TRUE 
 egghells TRUE 
 veggie TRUE 
 reggae TRUE 
  / pre> 
 
 
公式使包含广泛匹配而不是包含完全匹配。 eggisgood，鸡蛋和火腿，偷盗者，蛋壳，素食者和雷鬼项目应为FALSE。
 
 
 
我喜欢MATCH公式可能会让这个工作，但我不知道如何。
 
 
 
 更新：我不需要匹配项
解决方案
将公式更改为 = SUMPRODUCT（ -  ISNUMBER（SEARCH & good_food&;&A2））））> 0 解决了这个问题。 
 
In my first sheet I have a list which contains the following 10 items in Column A (One on row: A2-A11): 
egg
bootleggers egg
egg is good
egg and ham
eggs and hams
bootleggers
eggshells
veggie
reggae
I have a second sheet which contains a table (table name: good_food) with the following items:
egg
ham
I want to find out which items in the first sheet that contains an exact match from the list in the second sheet. 

This almost works just this formula:
=SUMPRODUCT(--ISNUMBER(SEARCH(good_food;A2)))>0
Though TRUE is returned on all items:
egg TRUE
bootleggers egg TRUE
egg is good TRUE
eggisgood   TRUE
egg and ham TRUE
eggs and hams   TRUE
bootleggers TRUE
eggshells   TRUE
veggie  TRUE
reggae  TRUE
The formula makes a "contains broad match" instead of an "contains exact match". The items "eggisgood", "eggs and hams", "bootleggers", "eggshells", "veggie" and "reggae" should be FALSE.

I am guessting the MATCH-formula might get this to work but I can't figure out how.

Update: I don't need to match the items in case sensitive.
解决方案
Changed the formula to =SUMPRODUCT(--ISNUMBER(SEARCH(" "&good_food&" ";" "&A2&" ")))>0 which solved it. 

                        
这篇关于Excel  - 匹配包含与列表完全匹配的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！