突出显示列A中与Excel中B列中任何行匹配的所有行 [英] Highlight all rows in Column A that match any row in Column B in Excel

问题描述

我有列A中的各个域和子域的链接集合。在列B中，我只有唯一的域名。我想要突出显示与列B中任何行匹配的所有行。

这是列A的示例数据

  abc.google.com 
 xyz.yahoo.com 
 www.facebook.com/testprofile 
 twitter.com/sampleprofile 
 www .hotfrog.com 
 www.businessinsider.com/sample-test-strory 
  

这是列B的样本数据

  google.com 
 aol.com 
 twitter.com 
 nytimes.com 
 huffingtonpost.com 
  

输出应突出显示
第一行和第四行，因为这两个只匹配列B。

解决方案

条件格式是正确的方式。

我正在使用您的示例数据，并假设它从A1开始

对于您的条件公式，您需要突出显示A1到您的数据结尾。

公式为：

  = NOT（ISERROR（LOOKUP（9E + 307，搜索（$ B $ 1：$ B $ 5，A1），$ B $ 1：$ B $ 5））
  

这样做是什么：

• 在A1中搜索B1：B5中的域列表（A1不固定，所以如果任何excel公式，它将被调整为后续单元格）。
  
  这样做的结果将是： {5，#VALUE !, #VALUE !, #VALUE !, #VALUE！ ，#VALUE！}


• 然后，我们在列表中查找小于9E + 307的最大值
  
  结果将是 5


• 然后我们测试是否有错误（如果没有找到域名，会发生错误）


• 最后，我们反转结果，所以如果找到一个值，函数将返回TRUE，如果没有，则返回FALSE。完成反转是因为条件突出显示只有当公式的结果为真时才突出显示。

示例，帮助列为 = LOOKUP（9E + 307，SEARCH（$ B $ 1：$ B $ 5，A1），$ B $ 1：$ B $ 5）所以你可以看到从主要部分返回的内容的条件格式公式：

I have set of links from various domains and subdomains in column A. And in column B I have unique domain names only. I want to highlight all rows that match any row in column B.

this is the sample data of Column A

abc.google.com
xyz.yahoo.com
www.facebook.com/testprofile
twitter.com/sampleprofile
www.hotfrog.com
www.businessinsider.com/sample-test-strory

This is sample data of Column B

google.com
aol.com
twitter.com
nytimes.com
huffingtonpost.com

And output should highlight Row one and Row four as these two only match from column B.

解决方案

Conditional formatting is the right way to go.

I'm using your example data, and assuming it starts from A1

For your conditional formula, you will need to highlight from A1 to the end of your data.

The formula would be:

=NOT(ISERROR(LOOKUP(9E+307,SEARCH($B$1:$B$5,A1),$B$1:$B$5)))

What this does is:

• Search your list of domains in B1:B5 for the text in A1 (A1 is not fixed in position, so as any excel formula, it will be adjusted for subsequent cells).
  The result from this would be: {5, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}
• We then look for the largest value in the list that is smaller than 9E+307
  The result would then be 5
• We then test if there was an error (which would happen if the domain is not found)
• Finally, we invert the result so the function will return TRUE if a value was found, and FALSE if not. Inversion is done because conditional highlighting only does the highlighting when the result of the formula is true

Example, with a helper column of =LOOKUP(9E+307,SEARCH($B$1:$B$5,A1),$B$1:$B$5) so you can see what is returned from the main part of the conditional format formula:

这篇关于突出显示列A中与Excel中B列中任何行匹配的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！