突出显示列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屋!